parser datetime via tsql

Question

Update List set Date = "2009-07-21T19:00:40"

sql server doesn't recognize this format. is there a conversion function

Answer 1

Try this:

UPDATE List SET Date = '2009/07/21 19:00:40'

Answer 2

You can use CAST and CONVERT. But maybe you must replace the 'T' with a space, before you can convert it. (There are also string manipulation functions available.)

Answer 3

Worked just fine for me (SQL Express 2005) except for the double quotes (which SQL Server assumed was a column delimiter); is that what's throwing the error? Thanks for the sample code, but can you produce the actual error?

In other words,

DECLARE @List TABLE ( [date] DATETIME )

INSERT INTO @List SELECT GETUTCDATE()

UPDATE @List SET Date = "2009-07-21T19:00:40"

produces

Msg 207, Level 16, State 1, Line 7 Invalid column name '2009-07-21T19:00:40'.

Whereas

DECLARE @List TABLE ( [date] DATETIME )

INSERT INTO @List SELECT GETUTCDATE()

UPDATE @List SET Date = '2009-07-21T19:00:40'

runs successfully.

Answer 4

Even worked for me too(2005 & 2008)..

DECLARE @tbl TABLE ( [date] DATETIME )
INSERT INTO @tbl SELECT getdate()
select * from @tbl

UPDATE @tbl SET Date = '2009-07-21T19:00:40'
select * from @tbl

However, just give a shot with DATEFORMAT COMMAND

Something like this

**SET DATEFORMAT dmy**

DECLARE @tbl TABLE ( [date] DATETIME )
INSERT INTO @tbl SELECT getdate()
select * from @tbl

UPDATE @tbl SET Date = '2009-07-21T19:00:40'
select * from @tbl