• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1160
  • Last Modified:

Convert to hh:mm:ss

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
Camillia
Asked:
Camillia
  • 2
  • 2
1 Solution
 
SharathData EngineerCommented:
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
 
CamilliaAuthor Commented:
yes, that worked. Whats missing from mine? just totally wrong or just gets the seconds?
0
 
SharathData EngineerCommented:
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
 
CamilliaAuthor Commented:
thanks, i have a related question and i will open a new question.
0

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now