Insert date string to datetime2 in TSQL insert

Question

I need to insert a date string as DATETIME2 in the QUEUE table in Microsoft Sql Server.

DB structure:

CREATE TABLE "QUEUE" (
    ID  INT PRIMARY KEY NOT NULL,
    TEAMID  VARCHAR(550) ,
    STATUS  VARCHAR(50) ,
    MSG VARCHAR(50) ,
    TIME    DATETIME2,
    ERROR   VARCHAR(10) ,
);

INSERT INTO QUEUE VALUES(2,'c33','ok','FoundID',CONVERT('Tue Sep 09 12:18:52 2014' AS DateTime2),'OK');

The value 'Tue Sep 09 12:18:52 2014' should be converted as datetime2 format like 2014-09-09 12:18:52.000000 and should be inserted.

I tried CAST and COVERT but it fails.

CONVERT('some string' AS DateTime2) is not valid syntax. Please google and learn about the CONVERT() function. — Tab Alleman, Jul 23 '15 at 15:51

score 5 · Accepted Answer · answered Jul 23 '15 at 16:02

5

As pointed out the syntax with the convert is a little off. The syntax should be like below:

SELECT CONVERT(datetime,'Sep 09 12:18:52 2014')

answered Jul 23 '15 at 16:02

Dane

312
2
10

score 0 · Answer 2 · answered Jul 24 '15 at 12:18

0

Dane's conversion works, but if the date is being passed as a field or variable, which includes a 3 letter day of the week at the beginning (like your example), it seems to still fail. Assuming the first 4 characters are always the day of the week and a space, you could tweak Dane's example using RIGHT and LEN.

SELECT CONVERT(datetime,RIGHT('Tue Sep 09 12:18:52 2014',LEN('Tue Sep 09 12:18:52 2014')-4))

answered Jul 24 '15 at 12:18

TLaV

389
2
4

Thanks @Tim LaVenice .. Ur answer was right to the point . This was the one i was searching for. The string conversion of the day was causing the problem. – Vijax Jul 24 '15 at 12:24

Insert date string to datetime2 in TSQL insert

2 Answers2