# Decimal value from Year Month Days

• ### 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

• 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 Wednesday, August 14, 2019 10:03 AM
• Unmarked as answer by Wednesday, August 14, 2019 10:04 AM
• Marked as answer by 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 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
Print @Result
---3.87```

• Marked as answer by Wednesday, August 14, 2019 10:04 AM
Tuesday, August 13, 2019 5:42 PM
• 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 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 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
Print @Result
---3.87```

• Marked as answer by Wednesday, August 14, 2019 10:04 AM
Tuesday, August 13, 2019 5:42 PM
• 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 Wednesday, August 14, 2019 10:03 AM
• Unmarked as answer by Wednesday, August 14, 2019 10:04 AM
• Marked as answer by 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 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
/*
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