none
How to convert millisecond to hour minute & second RRS feed

  • Question

  • see this is my code

    DECLARE @t1 DATETIME;
    DECLARE @t2 DATETIME;
    DECLARE @timetaken VARCHAR(20);
    
    SET @t1 = GETDATE();
    WAITFOR DELAY '00:00:02';
    SET @t2 = GETDATE();
    SELECT @timetaken = CAST(DATEDIFF(millisecond,@t1,@t2) AS VARCHAR(20))
    
    PRINT @timetaken 

    please share one function where i will pass my millisecond which return data in hour or minute or second

    may be it could 2minute : 10 second like this way. thanks

    Wednesday, August 14, 2019 8:47 AM

Answers

  • Hi Sudip_inn,

     

    Would you like this one ?

    DECLARE @t1 DATETIME;
    DECLARE @t2 DATETIME;
    DECLARE @timetaken VARCHAR(30);
    
    SET @t1 = GETDATE();
    WAITFOR DELAY '00:00:02';
    SET @t2 = GETDATE();
    SELECT  @timetaken = cast(DATEDIFF(hh,@t1,@t2)as varchar(10))+' hour:'+cast(DATEDIFF(mi,@t1,@t2)as varchar(10))
    +' minute:'+cast((DATEDIFF(ss,@t1,@t2)-DATEDIFF(mi,@t1,@t2)*60)as varchar(10))+' second'
    
    PRINT @timetaken 
    /*
    0 hour:0 minute:2 second
    */


    Hope it will help you.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Wednesday, August 14, 2019 9:18 AM
  • If you have two time values and the interval is less than 24 hours, then try this too:

       set @timetaken = CONVERT(varchar(30), @t2 - @t1, 108)

       print @timetaken -- displays '00:00:02'

     


    • Edited by Viorel_MVP Wednesday, August 14, 2019 11:23 AM
    • Marked as answer by Sudip_inn Friday, August 16, 2019 7:07 AM
    Wednesday, August 14, 2019 11:20 AM
    • Marked as answer by Sudip_inn Friday, August 16, 2019 7:07 AM
    Wednesday, August 14, 2019 12:56 PM
    Moderator
  • DECLARE @t1 DATETIME;
    DECLARE @t2 DATETIME;
    DECLARE @timetaken VARCHAR(30);
    
    SET @t1 = GETDATE();
    WAITFOR DELAY '00:00:02';
    SET @t2 = GETDATE();
    SELECT   @timetaken=
    Format(Dateadd(millisecond, DATEDIFF(millisecond,@t1,@t2),0),'H \hour: m \minu\te: s \secon\d')
     Print  @timetaken

    • Marked as answer by Sudip_inn Friday, August 16, 2019 7:08 AM
    Wednesday, August 14, 2019 1:45 PM
    Moderator

All replies

  • Hi Sudip_inn,

     

    Would you like this one ?

    DECLARE @t1 DATETIME;
    DECLARE @t2 DATETIME;
    DECLARE @timetaken VARCHAR(30);
    
    SET @t1 = GETDATE();
    WAITFOR DELAY '00:00:02';
    SET @t2 = GETDATE();
    SELECT  @timetaken = cast(DATEDIFF(hh,@t1,@t2)as varchar(10))+' hour:'+cast(DATEDIFF(mi,@t1,@t2)as varchar(10))
    +' minute:'+cast((DATEDIFF(ss,@t1,@t2)-DATEDIFF(mi,@t1,@t2)*60)as varchar(10))+' second'
    
    PRINT @timetaken 
    /*
    0 hour:0 minute:2 second
    */


    Hope it will help you.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Wednesday, August 14, 2019 9:18 AM
  • If you have two time values and the interval is less than 24 hours, then try this too:

       set @timetaken = CONVERT(varchar(30), @t2 - @t1, 108)

       print @timetaken -- displays '00:00:02'

     


    • Edited by Viorel_MVP Wednesday, August 14, 2019 11:23 AM
    • Marked as answer by Sudip_inn Friday, August 16, 2019 7:07 AM
    Wednesday, August 14, 2019 11:20 AM
    • Marked as answer by Sudip_inn Friday, August 16, 2019 7:07 AM
    Wednesday, August 14, 2019 12:56 PM
    Moderator
  • And WHY do you store the value returned by datediff in a varchar variable. You self-inflict many of your own problems. Do you NEED millisecond precision if your goal is to convert it to seconds.  Why not just calculate the difference in seconds? 
    Wednesday, August 14, 2019 1:15 PM
  • DECLARE @t1 DATETIME;
    DECLARE @t2 DATETIME;
    DECLARE @timetaken VARCHAR(30);
    
    SET @t1 = GETDATE();
    WAITFOR DELAY '00:00:02';
    SET @t2 = GETDATE();
    SELECT   @timetaken=
    Format(Dateadd(millisecond, DATEDIFF(millisecond,@t1,@t2),0),'H \hour: m \minu\te: s \secon\d')
     Print  @timetaken

    • Marked as answer by Sudip_inn Friday, August 16, 2019 7:08 AM
    Wednesday, August 14, 2019 1:45 PM
    Moderator