Exception when convert string to datetime in SQL Server

Question

In with statement:

WITH queryResult AS
(
    SELECT 
        CONVERT(DATETIME, '2017-12-12 ' + PARTIALDATE) 
    FROM 
        tableA 
    WHERE 
        ISDATE('2017-12-12 ' + PARTIALDATE) = 1
)
....

In this block, I will get exception:

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

So I made some changes:

WITH subQueryResult AS
(
    SELECT 
        PARTIALDATE 
    FROM 
        tableA 
    WHERE 
        ISDATE('2017-12-12 ' + PARTIALDATE) = 1
),
queryResult AS
(
    SELECT 
        CONVERT(DATETIME, '2017-12-12 ' + PARTIALDATE) 
    FROM 
        subQueryResult
)

And it works!

But why?

What is the datatype of the PARTIALDATE? my guess is.. the second query guarantees that partialdate is a date as non date rows are excluded.. hence the convert works — Harry
– Harry, Commented Jan 9, 2018 at 6:08
@Harry but in first SQL text, I have just the same where statement. — damon
– damon, Commented Jan 9, 2018 at 6:15
If you're using SQL Server 2012 or newer, you could check out the TRY_CAST / TRY_CONVERT commands - they return NULL, if something cannot be converted (instead of throwing an error) — marc_s
– marc_s, Commented Jan 9, 2018 at 6:28
I only get this error when the PartialDate is not suitable and ISDATE() clause is not used in the Select statement. So ithe first query seems to be quite OK for my test — Eralper
– Eralper, Commented Jan 9, 2018 at 6:28

gofr1 · Accepted Answer · 2018-01-09 09:18:23Z

1

The WHERE does not do the filtering before the conversion takes place. Order of operations is guaranteed for a case statement. The following should work:

WITH queryResult AS
(
    SELECT 
        CASE WHEN ISDATE('2017-12-12 ' + PARTIALDATE) = 1 THEN CONVERT(DATETIME, '2017-12-12 ' + PARTIALDATE) ELSE NULL END
    FROM 
        tableA 
    WHERE 
        ISDATE('2017-12-12 ' + PARTIALDATE) = 1
)

answered Jan 9, 2018 at 9:18

gofr1

16k11 gold badges47 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Exception when convert string to datetime in SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related