casting odd smallint time to to datetime format.
- by c6400sc
Hello everyone,
I'm working with a db (SQL server 2008), and have an interesting issue with times stored in the db.
The DBA who originally set it up was crafty and stored scheduled times as smallints in 12-hour form-- 6:00AM would be represented as 600. I've figured out how to split them into hours and minutes like thus:
select floor(time/100) as hr, right(time, 2) as min from table;
What I want to do is compare these scheduled times to actual times, which are stored in the proper datetime format. Ideally, I would do this with two datetime fields and use datediff() between them, but this would require converting the smallint time into a datetime, which I can't figure out.
Does anyone have suggestions on how to do this?
Thanks in advance.
