CODEWITHSUNDEEP
sql-serversql-server-2008
user_1330
user_1330

Reputation: 504

SQL Server, Incorrect syntax, Convert to datetime

Using ASP classic, I want to convert a date Tue, 21 Apr 2020 12:23:00 GMT to the format AAAA-MM-DD HH:MM:SS to insert it into a SQL Server 2008 database.

My code:

CONVERT('Tue, 21 Apr 2020 12:23:00 GMT', GETDATE(), 20)

I get this error:

Incorrect syntax to 'Tue, 21 Apr 2020 12:23:00 GMT'.

Upvotes: 1

Views: 269

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520898

Note that dates in SQL Server don't actually have any internal string format. Rather, they are stored as binary. One option here would be to take the following substring of the your input timestamp, and then use TRY_CONVERT to marshall it over to a bona fide datetime inside SQL Server:

Tue, 21 Apr 2020 12:23:00 GMT  <-- start with this input
21 Apr 2020 12:23:00           <-- convert this string to datetime

Sample code:

WITH yourTable AS (
    SELECT 'Tue, 21 Apr 2020 12:23:00 GMT' AS dt
)

SELECT
    dt,
    TRY_CONVERT(datetime, SUBSTRING(dt, 6, LEN(dt) - 9)) AS dt_out
FROM yourTable;

screen capture from demo link below

Demo

Edit:

If you are using an earlier version of SQL Server, then you can use CONVERT with format mask 106, using the same substring as above:

SELECT
    dt,
    CONVERT(datetime, SUBSTRING(dt, 6, LEN(dt) - 9), 106) AS dt_out
FROM yourTable;

Upvotes: 2

Related Questions