How to convert an integer value to time (HH:MM:SS) in SQL Server?

Tathagat Sharma 0
Viewed 85

I have a set of integer value which can be either a single digit to 6 digit number. Now I want to convert this set of values to time representing the HH:MM:SS format. I thought of converting first to varchar then to time but it didn't work out. Can anyone help me out with the problem?

tsql sql-server sql
3 Answers

You can use TIMEFROMPARTS

SELECT
  TIMEFROMPARTS(
    YourColumn / 10000,
    YourColumn / 100 % 100,
    YourColumn % 100
  )
FROM YourTable;
Charlieface0

This happens to be what run times look like in msdb..sysjobschedules, which I've addressed here. Assuming "val" is your integer, try:

select dateadd(s, val - ((val / 100) * 40) - ((val / 10000) * 2400), 0/*or some date*/)

(subtracting out 40 seconds per minute and 40*100 + 2400 seconds per hour to get the actual number of seconds, then adding that many seconds to a date.)

bwperrin0

Try:

date (dateadd(second,value,'19700101'))
AndreasWacker0
Related