24:00 as a time

[Leighton21]

Hi All,

I have a query which I am trying to get to work it is as follows

Select TIME
From TIMES
WHERE convert(datetime, runtime, 108) < '12:00'

unfortunately this is an existing DB and the runtime is a varchar with data that looks like the following

11:23
23:45

etc

one of the values is 24:00 (which is not actually a valid time. And since the user still wants to use the 24:00 I am at a cross road.

I tried something like
Select TIME
From TIMES
WHERE convert(datetime, (select case runtime when '24:00' then '00:00' ELSE runtime END from From TIMES), 108) < '12:00'

but it this still comes back with a error about conveeting a varchar to datetime being out of range.

Does anyone please have any suggestions?

 

[r937]

Select TIME
From TIMES
WHERE runtime='24:00'
OR convert(datetime, runtime, 108) < '12:00'


r937.com | rudy.ca

 

[Leighton21]

Cheers r937
Problem is the runtime value in the DB is exactly
'24:00'
So running this
convert(datetime, runtime, 108)

comes back with
Error conversion of varchar to datetime causes a out of range value ('24:00' should really be '00:00') however the user does not want to use '00:00'

Cheers

 

[r937]

WHERE CASE WHEN runtime='24:00'
           THEN '00:00'
           ELSE convert(datetime, runtime, 108) END
        < '12:00'

r937.com | rudy.ca

 

[rajeevnandanmishra]

And may be if you have DATE also stored in some column, then you need to add 1 day in case when the value of TIME field is 24:00

Just a thought after going through the thread...