How do you update a DateTime field in T-SQL?
SqlSql ServerTsqlTypesSql Problem Overview
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.
Sql Solutions
Solution 1 - Sql
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
Solution 2 - Sql
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
Solution 3 - Sql
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...
Solution 4 - Sql
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
Solution 5 - Sql
UPDATE TABLE
SET EndDate = CAST('2017-12-31' AS DATE)
WHERE Id = '123'
Solution 6 - Sql
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'
Solution 7 - Sql
That should work, I'd put brackets around [Date] as it's a reserved keyword.
Solution 8 - Sql
Is there maybe a trigger on the table setting it back?