Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Convert to hh:mm:ss

Posted on 2011-02-15
Medium Priority
1,156 Views
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
``````
0
Question by:Camillia
[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
• 2
• 2

LVL 41

Accepted Solution

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
``````
0

LVL 7

Author Comment

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

LVL 41

Expert Comment

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
-- 1900-01-02 00:20:10
``````

0

LVL 7

Author Comment

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

## Featured Post

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
###### Suggested Courses
Course of the Month7 days, 21 hours left to enroll