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

Question

I'm getting this error: Conversion failed when converting date and/or time from character string when I try to run the following query in SQL server 2008 R2:

    DECLARE @Time datetime = N'7/13/2011'
    DECLARE @str as varchar(100) = '2011_07_13_DM_VT_I2_Data'
    DECLARE @TestTime datetime =  cast(replace (left (left(@str, len(@str) - len('_data')), 10), '_', '') as datetime)
    DECLARE @r int = datediff(d, @TestTime, @Time)

    SELECT t.name FROM
    (
    SELECT 
         name
    FROM 
        sysobjects 
    WHERE 
        (type = 'U') AND ((name LIKE '%[_]I2[_]Data') or (name LIKE '%[_]R4[_]Data') or (name LIKE '%[_]R8[_]Data'))
    ) t

    WHERE
        datediff(DAY, cast(replace (left (left(t.name, len(t.name) - len('_data')), 10), '_', '') as datetime), @Time) <= 1

    ORDER BY 
        cast(replace (left (left(t.name, len(t.name) - len('_data')), 10), '_', '') as datetime) desc

This query runs fine if I comment out WHERE datediff(DAY, cast(replace (left (left(t.name, len(t.name) - len('_data')), 10), '_', '') as datetime), @Time) <= 1, then I wonder if it is the problem of the cast function. But the t table will have the table names like '2011_07_13_DM_VT_I2_Data' you can see in the @TestTime the cast function works fine with this format. I don't know what is the wrong with the WHERE condition.

Thank you for any help.

Mikael Eriksson · Accepted Answer · 2011-07-13 07:31:30Z

1

You need to split your query into two because it looks like the query optimizer has decided to apply the where clause from the outer query before it has filtered down the rows from sysobjects in the sub query.

You could try something like this.

DECLARE @Time datetime = N'7/13/2011'
DECLARE @str as varchar(100) = '2011_07_13_DM_VT_I2_Data'
DECLARE @TestTime datetime =  cast(replace (left (left(@str, len(@str) - len('_data')), 10), '_', '') as datetime)
DECLARE @r int = datediff(d, @TestTime, @Time)

SELECT name INTO #TMP
FROM 
    sysobjects 
WHERE 
    (type = 'U') AND ((name LIKE '%[_]I2[_]Data') or (name LIKE '%[_]R4[_]Data') or (name LIKE '%[_]R8[_]Data'))

SELECT *
FROM #TMP as t
WHERE
    datediff(DAY, cast(replace (left (left(t.name, len(t.name) - len('_data')), 10), '_', '') as datetime), @Time) <= 1

ORDER BY 
    cast(replace (left (left(t.name, len(t.name) - len('_data')), 10), '_', '') as datetime) desc

DROP TABLE #TMP

edited Jul 13, 2011 at 7:31

answered Jul 13, 2011 at 6:57

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

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

4 Comments

zdev Over a year ago

I tried to replace 10 with 11 in the CAST function but still the same error. But your query above works fine for me. There is actually no padding in the table names, the tables all start with 2011 without any prefix. It's odd.. But your query works for me, that is all matters for now. Thank you!

Mikael Eriksson Over a year ago

@zdev - Yes of course. I forgot that you could use brackets to create table with names like that [2011_07_13_DM_VT_I2_Data]. Then you can go back to use 10 instead of 11. It should still work. The reason it works with 11 as well is because you remove the last _ with the replace. I have updated the answer. The split of the query and use of a temp table is what fixed it for you.

zdev Over a year ago

I tried to use (type = 'U') AND (name LIKE '%[_]Data') in my original query it magically works fine, so I'm confused, the %[_]Data should filter less rows than using ((name LIKE '%[_]I2[_]Data') or (name LIKE '%[_]R4[_]Data') or (name LIKE '%[_]R8[_]Data')), But %[_]Data works fine, and the latter doesn't, do you know why?

Mikael Eriksson Over a year ago

@zdev - I think it is because the query optimizer decided to first filter down the rows using your like statement before it tried to apply the datediff condition. You really can't be sure in what order the where clauses will be evaluated. The error happens the query try to do a datediff for a table name that does not match your pattern.

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related