CODEWITHSUNDEEP
sqlsql-server
user1941944
user1941944

Reputation: 329

Converting seconds into HH:MM:SS format

How to convert seconds(int) into HH:MM:SS format in SQL Server?

e.g. 90 should output => 00:01:30

UPDATE

I have two dates START_DATE AND ENDDATE.

I want difference of these dates

Also finally I want to sum up these differences in HH:MM:SS format

FIDDLE: http://sqlfiddle.com/#!3/39c2b/1/0

Upvotes: 1

Views: 24345

Answers (7)

Eric Isaacs
Eric Isaacs

Reputation: 199

The easiest way to return HH:MM:SS string output from the differences of two DATETIME values is to use the built in T-SQL date/time functions. 

DECLARE
    @Start_Date DATETIME = DATEADD(SECOND, -86399, GETDATE())
  , @End_Date DATETIME = GETDATE();


SELECT 
    CONVERT (VARCHAR(8), 
    DATEADD(SECOND, DATEDIFF(SECOND, @Start_Date, @End_Date), 0), 108) 
    AS ElapsedHHMMSS;

OUTPUT: 23:59:59

Note however that if you're dealing with more than 1 day, you probably want a different output besides HH:MM:SS. This maxes out after 24 hours, but 24:00:01 isn't appropriate output anyway for this format. Some of the other responses above will produce an invalid 24:00:01 like output.

Upvotes: 0

rama
rama

Reputation: 481

This will generate a valid .NET timestamp. Code is non optimized though.

create function [dbo].to_timespan(@secs as int) returns nvarchar(16)
begin
    declare @mins int, @hours int, @days int;
    set @days = @secs / (60 * 60 * 24);
    set @secs = @secs - @days * (60 * 60 * 24); 
    set @hours = @secs / (60 * 60);
    set @secs = @secs - @hours * (60 * 60);
    set @mins = @secs / 60;
    set @secs = @secs - @mins * 60;
    return replace(str(@days, 2) + '.' + str(@hours, 2) + ':' + str(@mins, 2) + ':' + str(@secs, 2), ' ', '0');
end
GO

Upvotes: 0

Art
Art

Reputation: 5782

This may help - good example to subtract time portion and more. You can add/sum...:

Select start_date, end_date, time_diff,
   EXTRACT(DAY FROM time_diff) days,
   EXTRACT(HOUR FROM time_diff) hours,
   EXTRACT(MINUTE FROM time_diff) minutes,
   EXTRACT(SECOND FROM time_diff) seconds
From
(
Select start_date, end_date, end_date - start_date time_diff
From
(
Select CAST(to_date('21/02/2012 06:10:00 am', 'dd/mm/yyyy  hh:mi:ss am') AS TIMESTAMP)  end_date
     , CAST(to_date('01/01/2012 12:00:00 am', 'dd/mm/yyyy  hh:mi:ss am') AS TIMESTAMP) start_date
From dual
))
/

Upvotes: 0

jlee88my
jlee88my

Reputation: 3043

What you are referring to is the TIMESPAN concept in SQL. However, there is no TIMESPAN variable in T-SQL. Instead, you can use the following concept.

I would not use the ElapsedSecond as the storage variable (although it works). The reason is that to represent TIMESPAN effectively, it is best to store it as DATETIME. This way, you get all the flexibility of a DATETIME variable. For example, you can do a YEAR( @TimeSpan), MONTH( @TimeSpan ). It will be harder to do that if you have the ElapsedSecond stored.

To illustrate the concept in T-SQL:

DECLARE @StartDate AS DATETIME
DECLARE @EndDate AS DATETIME
SET @StartDate = '1/1/2013 1:01:58'
SET @EndDate = '1/5/2013 7:02:00'

DECLARE @TimeSpan AS DATETIME
SET @TimeSpan =  @EndDate - @StartDate

PRINT   CONVERT( VARCHAR, (DATEDIFF( DAY, 0, @TimeSpan) * 24) + DATEPART(HOUR, @TimeSpan) )
        + ':' + RIGHT( '0' + CONVERT( VARCHAR, DATEPART( MINUTE, @TimeSpan ) ), 2)
        + ':' + RIGHT( '0' + CONVERT( VARCHAR, DATEPART( SECOND, @TimeSpan ) ), 2 )

Notice that in the above, you can use the DATEPART() function and work on the TimeSpan just like any DATETIME variable. This is much more flexible and important as you get into more details.

Another advantage is a NEGATIVE Timespan. If you use the ElapsedSecond method, the function will fail, while a TimeSpan will survive.

Upvotes: 0

sourcecode
sourcecode

Reputation: 1802

      declare @sec int
      set @sec=10

      select
          convert(varchar(5),@sec/3600)
           +':'+convert(varchar(5),@sec%3600/60)
           +':'+convert(varchar(5),(@sec%60));

update
for data as hh:mm:ss use "replace" 

             declare @sec int
             set @sec=90

     select
          replace(convert(varchar(5),@sec/3600)
            +':'+str(convert(varchar(5),@sec%3600/60),2)
            +':'+str(convert(varchar(5),(@sec%60)),2),' ','0');


for example look here example

Upvotes: 3

Raj
Raj

Reputation: 10843

declare @Seconds int;
set @Seconds = 90;
select replace(str(@Seconds/3600,len(ltrim(@Seconds/3600))+
abs(sign(@Seconds/359999)-1)) + ':' + str((@Seconds/60)%60,2)+
':' + str(@Seconds%60,2),' ','0')

Upvotes: 2

Nipun Jain
Nipun Jain

Reputation: 601

    select to_char(to_date(90,'sssss'),'hh24:mi:ss') from dual;

try this mate

Upvotes: -1

Related Questions