問題描述
SQL Server 2008 ‑ 嘗試編輯日期 ‑ 語法錯誤？ (SQL Server 2008 ‑ Trying to edit dates ‑ Syntax Error?)
I have a database that has startDate and endDate. I am trying to run this simple script to find and replace certain dates. Here is my script:
SET startDate = '2012‑10‑11 07:00'
AND
endDate = '2012‑10‑13 20:00'
where startDate = '2012‑10‑12 07:00'
AND
endDate = '2012‑10‑14 20:00'
I run it and receive this error:
Incorrect syntax near '='.: SET startDate => '2012‑10‑11 07:00' AND endDate = '2012‑10‑13 20:00' where startDate = '2012‑10‑12 07:00' AND endDate = '2012‑10‑14 20:00'
I really have no idea why it's not running correctly, but hopefully one of you clever bod's here will be able to point out my incredibly dumb mistake!
Here's hoping! =0)
參考解法
方法 1:
you need to separate your columns to update with a comma rather than AND:
UPDATE YourTable
SET startDate = '2012‑10‑11 07:00',
endDate = '2012‑10‑13 20:00'
WHERE startDate = '2012‑10‑12 07:00'
AND endDate = '2012‑10‑14 20:00'
You should also use a culture insensitive date format like 'yyyyMMdd hh:mm:ss', e.g. your date 2012‑10‑11 07:00, would be interpretted as 11th October by some cultures, and 10th November by others.
Example on SQL Fiddle of why not use yyyy‑mm‑dd dates