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.

Solution

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