如何更新 T-SQL 中的 DateTime 字段？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3144074/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do you update a DateTime field in T-SQL?
提问by Xaisoft
The following query does not update the datetime field:
以下查询不会更新日期时间字段：
update table
SET EndDate = '2009-05-25'
WHERE Id = 1
I also tried it with no dashes, but that does not work either.
我也试过没有破折号，但这也不起作用。
回答by OMG Ponies
When in doubt, be explicit about the data type conversion using CAST/CONVERT:
如有疑问，请明确使用 CAST/CONVERT 进行数据类型转换：
UPDATE TABLE
SET EndDate = CAST('2009-05-25' AS DATETIME)
WHERE Id = 1
回答by Serkan Hekimoglu
Normally, it should work.
通常，它应该可以工作。
But can you try this? I don't have SQL on my home PC, I can't try myself
但是你能试试这个吗？我的家用电脑上没有 SQL，我无法自己尝试
UPDATE table
SET EndDate = '2009-05-25 00:00:00.000'
WHERE Id = 1
回答by Remus Rusanu
The string literal is pased according to the current dateformat setting, see SET DATEFORMAT. One format which will always work is the '20090525' one.
根据当前的日期格式设置传递字符串文字，请参阅SET DATEFORMAT。一种始终有效的格式是“20090525”格式。
Now, of course, you need to define 'does not work'. No records gets updated? Perhaps the Id=1doesn't match any record...
现在，当然，您需要定义“不起作用”。没有记录更新？也许Id=1不匹配任何记录...
If it says 'One record changed' then perhaps you need to show us how you verify...
如果它显示“一条记录已更改”，那么您可能需要向我们展示您如何验证...
回答by Moe Sisko
Using a DateTime parameter is the best way. However, if you still want to pass a DateTime as a string, then the CAST should not be necessary provided that a language agnostic format is used.
使用 DateTime 参数是最好的方法。但是，如果您仍想将 DateTime 作为字符串传递，那么如果使用了语言不可知格式，则不需要 CAST。
e.g.
例如
Given a table created like :
给定一个创建的表，如：
create table t1 (id int, EndDate DATETIME)
insert t1 (id, EndDate) values (1, GETDATE())
The following should always work :
以下应该始终有效：
update t1 set EndDate = '20100525' where id = 1 -- YYYYMMDD is language agnostic
The following will work :
以下将起作用：
SET LANGUAGE us_english
update t1 set EndDate = '2010-05-25' where id = 1
However, this won't :
但是，这不会：
SET LANGUAGE british
update t1 set EndDate = '2010-05-25' where id = 1
This is because 'YYYY-MM-DD' is not a language agnostic format (from SQL server's point of view) .
这是因为 'YYYY-MM-DD' 不是一种与语言无关的格式（从 SQL 服务器的角度来看）。
The ISO 'YYYY-MM-DDThh:mm:ss' format is also language agnostic, and useful when you need to pass a non-zero time.
ISO 'YYYY-MM-DDThh:mm:ss' 格式也与语言无关，当您需要传递非零时间时非常有用。
More info : http://karaszi.com/the-ultimate-guide-to-the-datetime-datatypes
更多信息：http: //karaszi.com/the-ultimate-guide-to-the-datetime-datatypes
回答by Evgeny Sobolev
UPDATE TABLE
SET EndDate = CAST('2017-12-31' AS DATE)
WHERE Id = '123'
回答by mikesl
That should work, I'd put brackets around [Date] as it's a reserved keyword.
这应该有效，我会在 [Date] 周围加上括号，因为它是一个保留关键字。
回答by Gratzy
Is there maybe a trigger on the table setting it back?
桌子上可能有一个触发器将其设置回来？
回答by OriginalOcto
If you aren't interested in specifying a time, you can also use the format 'DD/MM/YYYY', however I would stick to a Conversionmethod, and its relevant ISO format, as you really should avoid using default values.
如果您对指定时间不感兴趣，您也可以使用格式“ DD/MM/YYYY”，但是我会坚持使用转换方法及其相关的 ISO 格式，因为您确实应该避免使用默认值。
Here's an example:
下面是一个例子：
SET startDate = CONVERT(datetime,'2015-03-11T23:59:59.000',126)
WHERE custID = 'F24'
SET startDate = CONVERT(datetime,'2015-03-11T23:59:59.000',126)
WHERE custID = 'F24'
