Insert date string to datetime2 in TSQL insert

sql-server tsql insert datetime-format datetime2
Vijax picture Vijax · Jul 23, 2015 · Viewed 12.5k times · Source

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.

Answer

Dane picture Dane · Jul 23, 2015

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')

Related questions

Inserting multiple rows in a single SQL query?

I have multiple set of data to insert at once, say 4 rows. My table has three columns: Person, Id and Office. INSERT INTO MyTable VALUES ("John", 123, "Lloyds Office"); INSERT INTO MyTable VALUES ("Jane", 124, "Lloyds Office"); INSERT INTO MyTable VALUES ("Billy", 125, "…

Read More
Insert multiple rows WITHOUT repeating the "INSERT INTO ..." part of the statement?

I know I've done this before years ago, but I can't remember the syntax, and I can't find it anywhere due to pulling up tons of help docs and articles about "bulk imports". Here's what I want to do, but …

Read More
SELECT INTO a table variable in T-SQL

Got a complex SELECT query, from which I would like to insert all rows into a table variable, but T-SQL doesn't allow it. Along the same lines, you cannot use a table variable with SELECT INTO or INSERT EXEC queries. …

Read More