Solved

Convert Int value of seconds into a Time Stamp

Posted on 2008-10-24
4
322 Views
Last Modified: 2012-05-05
Hi ,

I have an Int field containing seconds that I need to convert into a Time Stamp.
I have managed to select
Select CONVERT(char(8), DATEADD(second, ActualDuration, ''), 108)
From
Table

But it does not give me a 24 hour clock in the sense that if I have a value of 173690
it is displaying as 00:14:50  
This is incorrect.
I need it to add on 24 hours per day, so if the int values of seconds goes into days I need the time stamp to display 48:00:00 for example when it's been 2 days.

Help would be appreciated....



0
Comment
Question by:EHardie
  • 3
4 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
Take a look at this user defined function you can create to emulate TimeSerial on MS SQL Server.  To make it function they way you want, just remove the % 24 part of this line:
SET @hours = floor(@total/3600) % 24

Here is the referenced link:
http://crossedlogic.blogspot.com/2008/09/group-by-time-timeserial-makes-return.html
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
And you will also need to remove the code to adjust from millitary time.  Here is a way to do it directly in SQL without UDF.

SELECT Cast((ActualDuration/3600) As nvarchar(2)) + ':' + RIGHT('0'+Cast(((ActualDuration % 3600)/60) As nvarchar(2)), 2)    SET @result = @result + ':' + RIGHT('0'+Cast(((ActualDuration % 3600) % 60) As nvarchar(2)),2)

Open in new window

0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
Comment Utility
Arg!  Sorry, got some residual text in the code snippet -- use this one.  Good thing I wrote the original, so I can copy all I want. ;)
SELECT Cast((ActualDuration/3600) As nvarchar(2)) + ':' + RIGHT('0'+Cast(((ActualDuration % 3600)/60) As nvarchar(2)), 2) + ':' + RIGHT('0'+Cast(((ActualDuration % 3600) % 60) As nvarchar(2)),2)

Open in new window

0
 

Author Closing Comment

by:EHardie
Comment Utility
Thanks this is perfect !
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now