none
decimal to time in sql RRS feed

  • Question

  • Hi All,

    Could anyone of you tell me how to convert decimal to time .

    I am using below code 

    declare @hour decimal(18,4)='249.2644444444'
    select  RIGHT('00' + CONVERT(varchar(2),FLOOR(@hour)),2)
    +':'
    + RIGHT('00' + CONVERT(varchar(2),FLOOR(((@hour-FLOOR(@hour))*60))),2)
    +':'
    + RIGHT('00' + CONVERT(varchar(2),FLOOR(((@hour-FLOOR(@hour))*60)-FLOOR(((@hour-FLOOR(@hour))*60)))*60),2)

    for example 1 it works fine but example 2 it throws arithmetic error. Could anyone of you suggest me right code which accepts any value and converts to time.

    Example 1 : 12.0763888889 as 12:04:00.

    Example 2: 249.2644444444 

    error : Arithmetic overflow error converting numeric to data type varchar.

    Thanks.

    Monday, September 7, 2015 12:07 PM

Answers

  • Try this:

    DECLARE @hour decimal(18,4)=249.2644444444 --12.0763888889
    
    
     select  RIGHT('00' + CONVERT(varchar(2), FLOOR(@hour % 24) ),2)
    +':'
    + RIGHT('00' + CONVERT(varchar(2),FLOOR(((@hour-FLOOR(@hour))*60))),2)
    +':'
    + RIGHT('00' + CONVERT(varchar(2),FLOOR(((@hour-FLOOR(@hour))*60)-FLOOR(((@hour-FLOOR(@hour))*60)))*60),2) 
    

    FLOOR(@hour / 24 ) is the number of days that has not to be displayed.

    FLOOR(@hour % 24) is the number of remaining hours.


    Please click "Mark As Answer" if my post helped.

    Monday, September 7, 2015 1:05 PM

All replies

  • Try using it as a datetime, you are trying to process hours into a field length of 2, and if you still require to pull out the parts, use the datepart/datediff functions.

    DECLARE @hour DECIMAL(28,12)=249.2644444444 
    SELECT (@hour%1)*60 TotalSeconds,
    FLOOR(@hour) TotalHours,
    DATEADD(SECOND,(@hour%1)*60,--Add seconds
    DATEADD(HOUR,FLOOR(@hour),--Add Hours
    '1900-01-01')) DateTime,
    DATEDIFF(HOUR,'1900-01-01',DATEADD(SECOND,(@hour%1)*60,DATEADD(HOUR,FLOOR(@hour),'1900-01-01'))),
    DATEPART(MINUTE,DATEADD(SECOND,(@hour%1)*60,DATEADD(HOUR,FLOOR(@hour),'1900-01-01'))),
    DATEPART(SECOND,DATEADD(SECOND,(@hour%1)*60,DATEADD(HOUR,FLOOR(@hour),'1900-01-01')))

    Gavin Clayton Claytabase Ltd www.claytabase.co.uk

    Monday, September 7, 2015 12:50 PM
  • Try this:

    DECLARE @hour decimal(18,4)=249.2644444444 --12.0763888889
    
    
     select  RIGHT('00' + CONVERT(varchar(2), FLOOR(@hour % 24) ),2)
    +':'
    + RIGHT('00' + CONVERT(varchar(2),FLOOR(((@hour-FLOOR(@hour))*60))),2)
    +':'
    + RIGHT('00' + CONVERT(varchar(2),FLOOR(((@hour-FLOOR(@hour))*60)-FLOOR(((@hour-FLOOR(@hour))*60)))*60),2) 
    

    FLOOR(@hour / 24 ) is the number of days that has not to be displayed.

    FLOOR(@hour % 24) is the number of remaining hours.


    Please click "Mark As Answer" if my post helped.

    Monday, September 7, 2015 1:05 PM