如何在 T-SQL 中更新 DateTime 字段？

以下查询不更新 datetime 字段:

update table
SET EndDate = '2009-05-25'
WHERE Id = 1

我也试过没有破折号，但这也没有工作。

489031 次浏览
小开

Normally, it should work.

But can you try this? I don't have SQL on my home PC, I can't try myself

UPDATE table
SET EndDate = '2009-05-25 00:00:00.000'
WHERE Id = 1
小开

Is there maybe a trigger on the table setting it back?

小开

That should work, I'd put brackets around [Date] as it's a reserved keyword.

小开

The string literal is pased according to the current dateformat setting, see SET DATEFORMAT. One format which will always work is the '20090525' one.

Now, of course, you need to define 'does not work'. No records gets updated? Perhaps the Id=1 doesn't match any record...

If it says 'One record changed' then perhaps you need to show us how you verify...

小开
最佳答案

When in doubt, be explicit about the data type conversion using CAST/CONVERT:

UPDATE TABLE
SET EndDate = CAST('2009-05-25' AS DATETIME)
WHERE Id = 1
小开

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.

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) .

The ISO 'YYYY-MM-DDThh:mm:ss' format is also language agnostic, and useful when you need to pass a non-zero time.

More info : http://karaszi.com/the-ultimate-guide-to-the-datetime-datatypes

小开

If you aren't interested in specifying a time, you can also use the format 'DD/MM/YYYY', however I would stick to a Conversion method, and its relevant ISO format, as you really should avoid using default values.

Here's an example:

SET startDate = CONVERT(datetime,'2015-03-11T23:59:59.000',126) WHERE custID = 'F24'

小开 
UPDATE TABLE
SET EndDate = CAST('2017-12-31' AS DATE)
WHERE Id = '123'

提交答案