CODEWITHSUNDEEP
sqlsql-server-2012
sky_limit
sky_limit

Reputation: 133

24hr time format string to sql time format

We have a column that stores a value in 24 hr time format. It is a string/text that users enter on to the interface. I need to convert it into sql time format so that I can do time difference calculations. How can I convert the string to time? Example: 

StringColumn   
1400
1600

needs to be

TimeColumn
1400
1600

so that I can calculate the time difference to get 2 hrs. Thanks.

Upvotes: 0

Views: 275

Answers (3)

HashSu
HashSu

Reputation: 1517

CAST(LEFT(Stringcolumn, 2) + ':' + RIGHT(LEFT(Stringcolumn, 4), 2) AS TIME)

Upvotes: 1

Turophile
Turophile

Reputation: 3405

You can do a conversion as in @jpw's answer, especially if you can use DATEDIFF on the results to get what you need.

Alternately you could perhaps do it as integer maths like:

SELECT (60*left('1900',2) + right('1900',2)) - (60*left('1400',2) + right('1400',2))

(I have used constants here, but you can replace '1900' and '1400' with column names).

Upvotes: 1

jpw
jpw

Reputation: 44871

If your string value are always 4 characters (meaning 01-09 and not 1-9 for early hours) then this works:

convert(time, stuff(StringColumn,3,0,':'))

Upvotes: 1

Related Questions