How do you update a DateTime field in T-SQL?

Question

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.

Answer 1

Normally, it should work. But can you please try this? I dont have SQL on my home pc, I cant try

update table

SET Date = '2009-05-25 00:00:00.000' WHERE Id = 1

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

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

Answer 6

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://www.karaszi.com/SQLServer/info_datetime.asp