CODEWITHSUNDEEP
sqlsql-server
Swazzy
Swazzy

Reputation: 97

Convert number of years into dateName?

So I'm calculating the datediff in years and getting a numerical value (Ex: 16.166666) What would be the best solution to turn that value into Date Names.

For example 16.166 years would turn into: 16 years and 1 month?

Upvotes: 1

Views: 38

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 81950

Example

Declare @Value float= 16.166

Select Years   = floor(@Value)
      ,Months  = floor((@Value - floor(@Value)) * 12)
      ,OrAsStr = ltrim(
                 replace(
                 replace(
                 concat(' ',floor(@Value),' Years ',floor((@Value - floor(@Value)) * 12),' Months')
                 ,' 1 Years ',' 1 Year ')
                 ,' 1 Months',' 1 Month')
                 )

Returns

Years   Months  OrAsStr
16      1       16 Years 1 Month

Upvotes: 2

Ilyes
Ilyes

Reputation: 14928

According to your comment

I have two date columns which I need to find the date difference , then convert to a string: Ex: 16 years and 1 month.

You can do like

DECLARE
       @Start DATE = '2000-01-01',
       @End   DATE = '2016-02-01';

SELECT CAST(T.Years AS VARCHAR(10)) + ' Years and ' + 
       CAST(DATEDIFF(Month, @Start, DATEADD(Year, - T.Years, @End)) AS VARCHAR(2)) + ' Months.'
FROM
    (
        SELECT DATEDIFF(Year, @Start, @End) Years
    ) T;

OR

SELECT CAST(DATEDIFF(Year, @Start, @End) AS VARCHAR(10)) + 
       ' Years and ' + 
       CAST(
             DATEDIFF(
                      Month, 
                      @Start, 
                      DATEADD(Year, - DATEDIFF(Year, @Start, @End), 
                      @End
                     )
           ) AS VARCHAR(2)) + ' Months.';

Returns

+------------------------+
| 16 Years and 1 Months. |
+------------------------+

Upvotes: 1

Related Questions