Solved

Convert Int value of seconds into a Time Stamp

Posted on 2008-10-24
4
333 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22798713
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
ID: 22798766
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
ID: 22798777
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
ID: 31509756
Thanks this is perfect !
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

763 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