Solved

Convert to hh:mm:ss

Posted on 2011-02-15
4
1,133 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 40

Accepted Solution

by:
Sharath earned 500 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 40

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

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 …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

746 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

13 Experts available now in Live!

Get 1:1 Help Now