49

This is not asking how to convert an arbitrary string to datetime in MSSQL such as this question.

I can control the string format but I want to know what the MSSQL syntax is for updating a datetime field using a date string.

Community
  • 1
  • 1
NeilG
  • 3,886
  • 2
  • 22
  • 30

2 Answers2

113
UPDATE MyTable SET MyDate = CONVERT(datetime, '2009/07/16 08:28:01', 120)

For a full discussion of CAST and CONVERT, including the different date formatting options, see the MSDN Library Link below:

https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql

Bork Blatt
  • 3,308
  • 2
  • 19
  • 17
28

For instance you can use

update tablename set datetimefield='19980223 14:23:05'
update tablename set datetimefield='02/23/1998 14:23:05'
update tablename set datetimefield='1998-12-23 14:23:05'
update tablename set datetimefield='23 February 1998 14:23:05'
update tablename set datetimefield='1998-02-23T14:23:05'

You need to be careful of day/month order since this will be language dependent when the year is not specified first. If you specify the year first then there is no problem; date order will always be year-month-day.

Adam
  • 6,041
  • 36
  • 120
  • 208
NeilG
  • 3,886
  • 2
  • 22
  • 30
  • 7
    -1. Year first does not guarantee y-m-d. SET LANGUAGE british GO SELECT CAST('2009-02-28' AS datetime), CAST('2009-02-28 01:02:03' AS datetime) --both fail – gbn Jul 16 '09 at 07:37
  • 2
    You have to use "date" and SQL Server 2008 before yyyy-mm-dd works. – gbn Jul 16 '09 at 07:39
  • 1
    Please be careful cutting and pasting. Tibor specifically refers to the new date types *only* for reliable ymd parsing – gbn Jul 16 '09 at 07:41
  • 1
    gbn - the example syntax does work regardless of language setting. The syntax you use was not part of the answer. A link is also provided with advice to read the full discussion. Quote: End result is that these new types are language neutral for a separated datetime literal as long as the year comes first. If that is the case, then the string will be interpreted as year first, then month and finally day - regardless of DATEFORMAT or language setting. – NeilG Jul 17 '09 at 01:36
  • gbn- thanks for correcting my use of 'MSSQL' instead of 'SQL Server'. I know now for future use. – NeilG Jul 17 '09 at 01:38
  • '2012-02-13 00:00:00' does not work for me on 2008R2. *The conversion of a varchar data type to a smalldatetime data type resulted in an out-of-range value.* I had to use `CONVERT` as Bork says. – Jarekczek Jun 10 '12 at 13:03