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

Question

Why does this statement succeed?

    SELECT CAST('08:50' as time)

But this one fails? tmrec is an nvarchar(6) column and contains the same value '08:50'. This is making me crazy since last 1 hour.

    SELECT   TOP 1 CAST(tmrec as time) 
    FROM     Instr 
    WHERE    igrp = 'JD' 
    ORDER BY ino , smallin

This screenshot shows the 1st query's result. It contains 08:50. Yet the 2nd query throws error.

Edit:

Even this doesn't work which guarantees that conversion is applied only on fetched records:

    SELECT CAST( tmrec as time)
    FROM
         (
            SELECT TOP 1 tmrec
            FROM   [ccwise-courts].[dbo].[INSTR]
            WHERE  igrp = 'JD' 
            ORDER BY ino , smallin
         ) v

I have to ask, why store "date" and "time" values as strings, instead of as ... dates and times? — Leigh
– Leigh, Commented Oct 18, 2012 at 4:37

RichardTheKiwi · Accepted Answer · 2012-10-16 11:11:48Z

3

Generally, to look for bad data, you can use a query like this:

SELECT TOP(100) '-->' + REPLACE(tmrec, ' ', '*') + '<--bad'
FROM Instr
WHERE ISDATE(tmrec) = 0

And if you still cannot make it out, you can list out the specific ASCII code of the characters involved (here I go up to 6 per the question):

SELECT TOP(100) '-->' + REPLACE(tmrec, ' ', '*') + '<--bad',
  Char1 = ascii(substring(tmrec,1,1)),
  Char2 = ascii(substring(tmrec,2,1)),
  Char3 = ascii(substring(tmrec,3,1)),
  Char4 = ascii(substring(tmrec,4,1)),
  Char5 = ascii(substring(tmrec,5,1)),
  Char6 = ascii(substring(tmrec,6,1))
FROM Instr
WHERE ISDATE(tmrec) = 0

There is this Connect item that deals with SQL Server processing CAST on the SELECT clause before the WHERE filter has been applied. To overcome that, as also noted in the bug report, you can use a CASE statement:

CAST(CASE WHEN ISDATE(tmrec)=1 THEN tmrec END as time)

edited Oct 16, 2012 at 11:11

answered Oct 16, 2012 at 10:17

RichardTheKiwi

108k28 gold badges206 silver badges269 bronze badges

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

5 Comments

Jack Over a year ago

I understand but what I don't understand is why would '08:50' doesn't work.

Jack Over a year ago

That row and column tmrec contains the same value '08:50'. I'll post screenshot in few mins.

Jack Over a year ago

@RichardTheKiwi: I get 48,56,58,53,48,NULL for tmrec field.

Jack Over a year ago

@RichardTheKiwi: Yes, correct. I replaced the 2nd query in screenshot with the ascii functions that you suggested.

Jack Over a year ago

Can't say how happy I am because I am pretty much sure without SO I couldn't never have been able to understand why it is failing :p. I would have kept banging my head against wall.

Collectives™ on Stack Overflow

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

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related