CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
Chandu
Chandu

Reputation: 23

Date Functions Conversions in SQL

Is there a way using TSQL to convert an integer to year, month and days

for e.g. 365 converts to 1year 0 months and 0 days 366 converts to 1year 0 months and 1 day

20 converts to 0 year 0 months and 20 days 200 converts to 0 year 13 months and 9 days 408 converts to 1 year 3 months and 7 days .. etc

Upvotes: 1

Views: 176

Answers (2)

Paul Maxwell
Paul Maxwell

Reputation: 35573

I don't know of any inbuilt way in SQL Server 2008, but the following logic will give you all the pieces you need to concatenate the items together:

select
     n
   , year(dateadd(day,n,0))-1900 y
   , month(dateadd(day,n,0))-1 m
   , day(dateadd(day,n,0))-1 d
from (
    select 365 n union all
    select 366 n union all
    select 20 n union all
    select 200 n union all
    select 408 n
    ) d

| n     | y | m | d  |
|-------|---|---|----|
| 365   | 1 | 0 |  0 |
| 366   | 1 | 0 |  1 |
|  20   | 0 | 0 | 20 |
| 200   | 0 | 6 | 19 |
| 408   | 1 | 1 | 12 |

Note that zero used in in the DATEDADD function is the date 1900-01-01, hence 1900 is deducted from the year calculation.

Thanks to Martin Smith for correcting my assumption about the leap year.

Upvotes: 2

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You could try without using any functions just by dividing integer values if we consider all months are 30 days:

DECLARE @days INT;
SET @days = 365;

SELECT [Years] = @days / 365,
       [Months] = (@days % 365) / 30,
       [Days] = (@days % 365) % 30;

@days = 365 

Years   Months  Days
1       0       0

@days = 20

Years   Months  Days
0       0       20

Upvotes: 1

Related Questions