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

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.

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

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

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

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'

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

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