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

User · Answer

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

User · Answer

Is there maybe a trigger on the table setting it back

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

That should work  I d put brackets around  Date  as it s a reserved keyword

User · Answer

UPDATE TABLE    SET EndDate   CAST  2017-12-31  AS DATE   WHERE Id    123

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

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to types