chrj15 chrj15 - 1 year ago 141
SQL Question

Converting Numeric to Time

I have a column which contains NUMERIC(5,2) data. The data is representative of shift start times. A few examples are 6.30, 10.30, 13.30 and 15.30. What's the best way to convert this into a time so I can do time calculations against another field? The other field is a datetime field.

Edit: The values represent times. For example 6.30 = 06:30, 15.30 = 03:30 PM.

Answer Source

You can do something like this:

select dateadd(minute, floor(col) * 60 + (col % 1) * 100, 0)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download