How to convert a number to time

Transact-SQL
1

I have 2 columns on my table that contains time in and out. I need to convert those columns to real time format to subtract them and get the spent hours

TimeIn     TimeOut 
150          1930
615          1747
410          1830
400          1600

these columns should look like

 TimeIn     TimeOut 
1:50           19:30
6:15           17:47
4:10           1830
4:00           16:00

or

 TimeIn          TimeOut 
1:50 am          7:30pm
6:15 am          5:47pm
4:10 am          6:30pm
4:00 am          4:00pm

how I can accomplish this?

thanks

2 
SELECT DATEDIFF(mi,
	CAST(STUFF(RIGHT('0'+CAST([TimeIn] AS VARCHAR(8)),4),3,0,':') AS DATETIME),
	CAST(STUFF(RIGHT('0'+CAST([TimeOut] AS VARCHAR(8)),4),3,0,':') AS DATETIME)
	)/60.0 AS [Hours]	
FROM YourTable;
1 Like
3

that is returning wrong values
17.66
11.53
13.25
12.83

4

Looks fine to me, post some additional sample data in this code snippet of values that are not working for you.

It would be apprecaited if, in future, you post sample code like this - otherwise all of us wind up spending time, individually, creating our own rather than reusing yours


SELECT *
INTO #TEMP
FROM (
SELECT [TimeIn]=150, [TimeOut]=1930
UNION ALL SELECT 615, 1747
UNION ALL SELECT 410, 1830
UNION ALL SELECT 400, 1600
) AS X

SELECT	TimeIn,
	CAST(STUFF(RIGHT('0'+CAST([TimeIn] AS VARCHAR(8)),4),3,0,':') AS DATETIME) AS [NewTimeIn],
	TimeOut,
	CAST(STUFF(RIGHT('0'+CAST([TimeOut] AS VARCHAR(8)),4),3,0,':') AS DATETIME) AS [NewTimeOut],
	DATEDIFF(mi,
	CAST(STUFF(RIGHT('0'+CAST([TimeIn] AS VARCHAR(8)),4),3,0,':') AS DATETIME),
	CAST(STUFF(RIGHT('0'+CAST([TimeOut] AS VARCHAR(8)),4),3,0,':') AS DATETIME)
	)/60.0 AS [HoursDifference]	
FROM #TEMP

SELECT	TimeIn, [NewTimeIn]=CONVERT(time, STUFF(CONVERT(varchar(10), TimeIn), LEN(TimeIn)-1, 0, ':')),
	TimeOut, [NewTimeOut]=CONVERT(time, STUFF(CONVERT(varchar(10), TimeOut), LEN(TimeOut)-1, 0, ':'))
FROM #TEMP
GO
DROP TABLE #TEMP
GO
5

P.S. if you are on a recent (SQL2008 or later) version of SQL then CASTing to TIME rather than DATETIME will look neater onscreen.

6

I wouldn't mess with text conversions -- that will just slow you down.

SELECT ...,
((TimeOut / 100 * 60 + TimeOut % 100) -
(TimeIn / 100 * 60 + TimeIn % 100)) / 60.0 AS Hours_Diff

3 Likes
7

I might be wrong, but I think op wants timein, timeout shown aswell as the "spent hours". Also I think he/she wants all these fields formated as HH:MM.
If I'm right, this could do the trick:

select stuff(right('000'+cast(timein as varchar(4)),4),3,0,':') as timein
      ,stuff(right('000'+cast(timeout as varchar(4)),4),3,0,':') as timeout
      ,stuff(right('000'+cast(((timeout/100*60+timeout%100)
                              +case when timein>timeout then 1440 else 0 end
                              -(timein/100*60+timein%100)
                              )/60*100
                             +((timeout/100*60+timeout%100)
                              +case when timein>timeout then 1440 else 0 end
                              -(timein/100*60+timein%100)
                              )%60
                             as varchar(4)
                             )
                  ,4
                  )
            ,3,0,':'
            )
       as timediff
  from yourtable

Edit: added option to go beyond midnight - ex. 1600 - 0400

8

I'd change the TimeIn and TimeOut columns to Time (or DateTime if pre SQL2008) and convert the data into Time format on the way in. Saves all this messing around every time they are displayed.

9

How about:

declare @ table (TimeIn int, TimeOut  int)
insert into @(TimeIn, TimeOut) values

(150, 1930),
(615, 1747),
(410, 1830),
(400, 1600)

select TimeIn_time, TimeOut_time, Spent_time
from @
cross apply
(
    select cast(dateadd(minute,TimeIn/100*60+TimeIn%60,0) as Time) 
         , cast(dateadd(minute,TimeOut/100*60+TimeOut%60,0) as Time) 
         , cast(dateadd(minute,(TimeOut-TimeIn)/100*60+(TimeOut-TimeIn)%60,0) as Time) 
) _(TimeIn_time, TimeOut_time, Spent_time)