Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to convert integer to hours,mins and seconds Expand / Collapse
Author
Message
Posted Wednesday, February 2, 2011 9:28 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 1:27 PM
Points: 42, Visits: 107
Hi,

I have the following integer 80010, All I want to do is convert this to
08:00:10 so I can use in a DateDiff function.

Is there a time function which will do this or do I have to resort to breaking the number down and appending the ':'?

Thanks
Post #1057562
Posted Wednesday, February 2, 2011 9:39 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 8:14 AM
Points: 845, Visits: 5,454
Maybe:
DECLARE @time int, @dt datetime;
SET @time = 80010;
SET @dt = DATEADD(second, 80010%100,DATEADD(minute, 80010%10000/100,DATEADD(hour, 80010/10000,'19000101')));

SELECT @dt

Post #1057565
Posted Wednesday, February 2, 2011 10:07 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 1:27 PM
Points: 42, Visits: 107
Thanks.
I already have a date in a different column and I trying to calculate the number of minutes worked by an employee on a given date. I have the starttime as 80010 (integer) and endtime as 161500. The result I am looking for is 495 minutes. I believe I can achieve this result if I was able to convert startdate 80010 to 08:00:10 and endate to 16:15:00 and then using the datediff function to get the results in minutes.
Post #1057585
Posted Wednesday, February 2, 2011 10:56 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 8:14 AM
Points: 845, Visits: 5,454
Why bother with functions?:
DECLARE @starttime int = 80010
,@endtime int = 161500

SELECT ((@endtime/10000 * 60) + (@endtime%10000/100))
- ((@starttime/10000 * 60) + (@starttime%10000/100))

Post #1057612
Posted Wednesday, February 2, 2011 11:04 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 1:27 PM
Points: 42, Visits: 107
Thanks
Post #1057618
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse