none
Decimal value from Year Month Days RRS feed

  • Question

  • Hello All, How do I get a Decimal value from Year Month Days

    For example - 3y 10m 13d

    I have tried few things and below query is the closet I get but still doesn't provide me the expected results.

    Declare 
    @year int = 3
    ,@month int =10
    ,@day int =13
    ,@Result varchar(50)

    set @Result= 
    CAST(@year+CAST(ROUND((@month+0.00)/12.0,1)as numeric(18,1)) as VARCHAR(10))+RIGHT(CAST(CAST(ROUND((@day+0.00)/30.0,1)as numeric(18,1)) as VARCHAR(10)),1)

    select @Result

    Result - 3.84
    Expected - 3.87 
    3 year + 0.83 month + 0.04 days

    Much Thanks,

    Pragati


    Best Regards, Pragati

    Tuesday, August 13, 2019 4:25 PM

Answers

  • Hi Pragati, 

    Try this:  

    Declare 
    @year int = 3
    ,@month int =10
    ,@day int =13
    ,@Result varchar(50)
    
    set @Result= ( @year*360 + @month *30 + @day )*1.0 /360
    select @Result	
    
    /*
    -------------
    3.869444
    */

    Sabrina


    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.

    • Marked as answer by Pragati Sharma Wednesday, August 14, 2019 10:03 AM
    • Unmarked as answer by Pragati Sharma Wednesday, August 14, 2019 10:04 AM
    • Marked as answer by Pragati Sharma Wednesday, August 14, 2019 10:04 AM
    Wednesday, August 14, 2019 3:20 AM
  • Perhaps (simplifying the expression and counting a day as 1/365 of a year.

    set @result =
    @year+CAST(ROUND(@month/12.0,2)as numeric(18,2))+CAST(ROUND(@day/365.0,2) as numeric(18,2))
    select @Result
    Tom
    • Marked as answer by Pragati Sharma Wednesday, August 14, 2019 10:05 AM
    Tuesday, August 13, 2019 5:29 PM
  •  
    Declare 
    @year int = 3
    ,@month int =10
    ,@day int =13
    ,@Result varchar(50)
    
    
    Select 
    --dateadd(year,datediff(year,0,getdate())-@year,0)
    @Result=Format(@year+1.0*Datediff(day,dateadd(year,datediff(year,0,getdate()),0),Dateadd(Day,@day,Dateadd(month,@month,dateadd(year,datediff(year,0,getdate()),0))))
    /Datediff(day,dateadd(year,datediff(year,0,getdate()),0),dateadd(year,datediff(year,0,getdate())+1,0) ) ,'##.##')
    Print @Result
    ---3.87

    • Marked as answer by Pragati Sharma Wednesday, August 14, 2019 10:04 AM
    Tuesday, August 13, 2019 5:42 PM
    Moderator
  • This variation seems to work too:

     

    declare @year   int = 3,

            @month  int = 10,

            @day    int = 13,

            @Result varchar(50)

     

    declare @r decimal(10,2)

     

    set @r = @year + @month * (1.0/12) + @day * (1.0/365)

    set @Result = FORMAT(@r, '0.00')

     

    select @r, @Result

    • Marked as answer by Pragati Sharma Wednesday, August 14, 2019 10:03 AM
    Wednesday, August 14, 2019 6:01 AM

All replies

  • Perhaps this?

    Declare 
    @year int = 3
    ,@month int =10
    ,@day int =13
    ,@Result varchar(50)
    
    
    select  @Result= convert(varchar(3),@year+CAST(ROUND((@month+0.00)/12.0,1)as numeric(18,1)) )+ RIGHT(CAST(CAST(ROUND((@day+0.00)/30.0,1)as numeric(18,1)) as VARCHAR(10)),1) 
    
    select @Result
    

    Tuesday, August 13, 2019 4:39 PM
  • DECLARE @year int = 3,
    		@month int = 10,
    		@day int = 13,
    		@Result varchar(50);
    
    SELECT CAST(CAST(@year + CAST(@month * 30 + 12 AS decimal(5,2)) / 365 AS decimal(5, 2)) AS varchar(50));
    SELECT @Result;

    But I do not think you can get the accurate value since 10 months could be 302, 303 or 305 days:

    SELECT  DATEDIFF(day, '2018-01-01', '2018-10-31'), 
    		DATEDIFF(day, '2018-02-01', '2018-11-30'),
    		DATEDIFF(day, '2018-03-01', '2018-12-31');


    A Fan of SSIS, SSRS and SSAS

    Tuesday, August 13, 2019 4:51 PM
  • I am expecting the result to be 3.87 . I already get 3.84 from the query I have posted above.

    Thanks,

    Pragati


    Best Regards, Pragati

    Tuesday, August 13, 2019 5:05 PM
  • Perhaps (simplifying the expression and counting a day as 1/365 of a year.

    set @result =
    @year+CAST(ROUND(@month/12.0,2)as numeric(18,2))+CAST(ROUND(@day/365.0,2) as numeric(18,2))
    select @Result
    Tom
    • Marked as answer by Pragati Sharma Wednesday, August 14, 2019 10:05 AM
    Tuesday, August 13, 2019 5:29 PM
  •  
    Declare 
    @year int = 3
    ,@month int =10
    ,@day int =13
    ,@Result varchar(50)
    
    
    Select 
    --dateadd(year,datediff(year,0,getdate())-@year,0)
    @Result=Format(@year+1.0*Datediff(day,dateadd(year,datediff(year,0,getdate()),0),Dateadd(Day,@day,Dateadd(month,@month,dateadd(year,datediff(year,0,getdate()),0))))
    /Datediff(day,dateadd(year,datediff(year,0,getdate()),0),dateadd(year,datediff(year,0,getdate())+1,0) ) ,'##.##')
    Print @Result
    ---3.87

    • Marked as answer by Pragati Sharma Wednesday, August 14, 2019 10:04 AM
    Tuesday, August 13, 2019 5:42 PM
    Moderator
  • Hi Pragati, 

    Try this:  

    Declare 
    @year int = 3
    ,@month int =10
    ,@day int =13
    ,@Result varchar(50)
    
    set @Result= ( @year*360 + @month *30 + @day )*1.0 /360
    select @Result	
    
    /*
    -------------
    3.869444
    */

    Sabrina


    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.

    • Marked as answer by Pragati Sharma Wednesday, August 14, 2019 10:03 AM
    • Unmarked as answer by Pragati Sharma Wednesday, August 14, 2019 10:04 AM
    • Marked as answer by Pragati Sharma Wednesday, August 14, 2019 10:04 AM
    Wednesday, August 14, 2019 3:20 AM
  • This variation seems to work too:

     

    declare @year   int = 3,

            @month  int = 10,

            @day    int = 13,

            @Result varchar(50)

     

    declare @r decimal(10,2)

     

    set @r = @year + @month * (1.0/12) + @day * (1.0/365)

    set @Result = FORMAT(@r, '0.00')

     

    select @r, @Result

    • Marked as answer by Pragati Sharma Wednesday, August 14, 2019 10:03 AM
    Wednesday, August 14, 2019 6:01 AM
  • Wow :)

    Thank you so much everyone!!


    Best Regards, Pragati

    Wednesday, August 14, 2019 10:06 AM
  • For two digits precision, all these answers should work. But if you look more digits, they are a little different.

    Declare 
    @year int = 3
    ,@month int =10
    ,@day int =13
    ,@Result varchar(50)


    Select 
    --dateadd(year,datediff(year,0,getdate())-@year,0)
      @year+1.0*Datediff(day,dateadd(year,datediff(year,0,getdate()),0),Dateadd(Day,@day,Dateadd(month,@month,dateadd(year,datediff(year,0,getdate()),0))))
    /Datediff(day,dateadd(year,datediff(year,0,getdate()),0),dateadd(year,datediff(year,0,getdate())+1,0) )  
     /*
    3.868493150684
    */

    Select ( @year*360 + @month *30 + @day )*1.0 /360
     /*
    3.869444
    */

    select   @year + @month * (1.0/12) + @day * (1.0/365)
    /*
    3.868937
    */

     
    Wednesday, August 14, 2019 3:24 PM
    Moderator