Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Convert to hh:mm:ss

Posted on 2011-02-15
4
Medium Priority
?
1,158 Views
Last Modified: 2012-05-11
This is SQL 2000.

I have a value that's in seconds (now it could be milliseconds but since i cant see the code, i'm not sure which). I think it's millisecond...

I have below and it converts the value to 00:20:10 but i think it should be 24:20:10..that's why i think the value is actually in milliseconds..not seconds...how can I fix this (convert mlillisecond to hh:mm:ss?)
declare @test as int
set @test = 87610 -- i think this is millisecond but the output should be 24:20:10 NOT 00:20:10
select convert(varchar(8),dateadd(ss,isnull((@test),0),'00:00:00'),108) Avg_Response_Time

Open in new window

0
Comment
Question by:Camillia
  • 2
  • 2
4 Comments
 
LVL 41

Accepted Solution

by:
Sharath earned 2000 total points
ID: 34900509
Can you check this?

declare @test int
set @test = 87610
select case when @test/3600 < 10 then '0' + convert(varchar(10),@test/3600) else convert(varchar(10),@test/3600) end + ':' +
       case when (@test%3600)/60 < 10 then '0' + convert(varchar(10),(@test%3600)/60) else convert(varchar(10),(@test%3600)/60) end + ':' +
       case when @test%60 < 10 then '0' + convert(varchar(10),@test%60) else convert(varchar(10),@test%60) end
-- 24:20:10

Open in new window

0
 
LVL 7

Author Comment

by:Camillia
ID: 34900526
yes, that worked. Whats missing from mine? just totally wrong or just gets the seconds?
0
 
LVL 41

Expert Comment

by:Sharath
ID: 34900617
in the format HH:MI:SS, the hours cannot exceed 24. At max the value would be 23:59:59 and after that the day will be increment to one resetting the HH:MI:SS to start from 00:00:00 for next day.
In your case 24:20:10 won;t be displayed instead the day would be incremented and time part would be displayed as 00:20:10

Run this and see the day got changed to 02.

declare @test as int
set @test = 87610 -- i think this is millisecond but the output should be 24:20:10 NOT 00:20:10
select convert(varchar(50),dateadd(SECOND,isnull((@test),0),'00:00:00'),120) Avg_Response_Time
-- 1900-01-02 00:20:10

Open in new window


0
 
LVL 7

Author Comment

by:Camillia
ID: 34900627
thanks, i have a related question and i will open a new question.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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

876 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