Failed conversion from nvarchar to datetime

Question

I am trying to convert an nvarchar date into a date time, but this error occurs: I have tried multiple ways including CAST and Convert (as code below) with no avail. Any suggestions ?

Date Format : Wed, 19 Jul 2017 16:23:38 +0000

Code:

INSERT INTO feed.article(title,link,sourceID,[date])
    SELECT title,link,s.sourceID,
    CONVERT(DATETIME,[date],121)
    FROM feed.tempXML t
    JOIN feed.[source] s ON s.sourceName = t.[source]

Error given:

Conversion failed when converting date and/or time from character string.

Use a combination of `LEFT` and `RIGHT` to remove `Wed, ` and `+0000`. A value of `'19 Jul 2017 16:23:38'` will successfully convert. — Tyler Roper, Jul 19 '17 at 18:23
Use this to help you with this issue https://learn.microsoft.com/en-us/sql/t-sql/functions/isdate-transact-sql — Neo, Jul 19 '17 at 18:30
The timezone offset (the +0000) is an artifact of UTC (universal coordinated time, the actual acronym is in French which is why the letter order doesn't match) and won't work in sql server. But pay attention to whether or not it's populated... if your source data is using UTC conventions, then that may contain information that you'll need to handle. — Ryan B., Jul 19 '17 at 18:30

Oleg Belousov · Answer 1 · 2017-07-19T18:41:53.587

2

If you have MS SQL Server 2012 or higher, you may use TRY_PARSE.

SELECT CAST(TRY_PARSE ('Wed, 19 Jul 2017 16:23:38 +0000' AS datetimeoffset) AS datetime)

edited Jul 19 '17 at 18:41

answered Jul 19 '17 at 18:37

Oleg Belousov

509
3
8

score 1 · Accepted Answer · edited Jul 19 '17 at 18:41

1

I would do this in two parts, one for the date and one for the time:

SELECT title,link,s.sourceID,
       (CONVERT(DATETIME, SUBSTRING([date], 5, 10), 106) +
        CONVERT(DATETIME, SUBSTRING([date], 18, 8))
       )
FROM feed.tempXML t JOIN
     feed.[source] s
     ON s.sourceName = t.[source];

This minimizes the string operations, so it seems like a pretty simple approach.

edited Jul 19 '17 at 18:41

Tyler Roper

21,445
6
33
56

answered Jul 19 '17 at 18:40

Gordon Linoff

1,242,037
58
646
786

Tyler Roper · Answer 3 · 2017-07-19T18:44:32.377

EDIT: Check out the solutions by Oleg and Gordon. I actually prefer them both to my own (as it's quite convoluted).

You need to get your date format from this...

'Wed, 19 Jul 2017 16:23:38 +0000'

...to this...

'19 Jul 2017 16:23:38'

You can remove chars from the beginning and end using LEFT and RIGHT. Removing the last 6 from the end would look like this:

LEFT([date], LEN[date] - 6)

We can use the same syntax for our RIGHT() to remove the first 5, but [date] must now be replaced with the entire string from above:

-- RIGHT([date], LEN([date]) - 5) becomes...
RIGHT(LEFT(@d, LEN(@d) - 6), LEN(LEFT(@d, LEN(@d) - 6)) - 5)

All in all, it's ugly, but works:

INSERT INTO feed.article(title,link,sourceID,[date])
SELECT title,link,s.sourceID,
CONVERT(DATETIME,RIGHT(LEFT([date], LEN([date]) - 6), LEN(LEFT([date], LEN([date]) - 6)) - 5),121)
FROM feed.tempXML t
JOIN feed.[source] s ON s.sourceName = t.[source]

IMPORTANT NOTE: This is under the assumption that the format of your date will always have 5 unnecessary characters at the beginning, and that your timezone offset (the +0000 at the end) will always be 0 (so we can simply ignore it).

If you'll have values that make use of the timezone offset, you'll need to account for that.

score 0 · Answer 4 · answered Jul 19 '17 at 18:45

    Based on the format, we should be able make a few "safe assumptions"...
    1) The weekday will always be expressed as a 3 char abbreviation.
    2) The 3 char abbreviation will be followed by a comma and a space.
    3) The portion of code we're interested in will be either 19 or 20 characters. 
        (10 for single digit dates and 20 for double digit dates)
    4) There will be a space following the date.

    Based on these assumptions, you should be safe to use the following...

        CREATE TABLE #TestData (
            StringDate NVARCHAR(40) NOT NULL 
            );
        INSERT #TestData (StringDate) VALUES
            (N'Wed, 19 Jul 2017 16:23:38 +0000'),
            (N'Wed, 9 Jul 2017 16:23:38 +0000');

        SELECT 
            DateTimeDate = CAST(SUBSTRING(td.StringDate, 6, 20) AS DATETIME)
        FROM 
            #TestData td;

Failed conversion from nvarchar to datetime

4 Answers4