CODEWITHSUNDEEP
sqlsql-server-2012
chrj15
chrj15

Reputation: 51

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.

Upvotes: 0

Views: 235

Answers (2)

Pரதீப்
Pரதீப்

Reputation: 93694

Replace the dot(.) with colon(:) and compare against your time column.

select * 
from yourtable 
Where timecol = replace(numericcol,'.',':')

considering timecol is of datatype time, rhs will be implicitly converted to time.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You can do something like this:

select dateadd(minute, floor(col) * 60 + (col % 1) * 100, 0)

Upvotes: 2

Related Questions