Convert an integer to year month and days in SQL server

Convert an integer to year month and days in SQL server

In this article, how to an integer to year month and days in SQL server, If you wanted something like elapsed time, you really have to do it between two dates, and not from an integer the elapsed number of months depends on whether the month has 28 to 31 days in it; same for a year some years have 365, leap years have 366 days.

SELECT  [YEARS ]        = DATEDIFF(YEAR,0,ET-ST)-1,

        [MONTHS]        = DATEPART(MONTH,ET-ST)-1,

        [DAYS]          = DATEPART(DAY,ET-ST)-1,

        [HOURS]         = DATEPART(HOUR,ET-ST),

        [MINUTES]       = DATEPART(MINUTE,ET-ST),

        [SECONDS]       = DATEPART(SECOND,ET-ST),

        [Milliseconds]  = DATEPART(millisecond,ET-ST)

FROM
        (

        SELECT 

                ST = CONVERT(datetime,'2008/09/22 00:35:33.997'),

                ET = CONVERT(datetime,'2009/10/23 04:05:45.443')

        ) abc

Output

YEARS  MONTHS DAYS HOURS MINUTES SECONDS Milliseconds
0             1               0        3           30           11              447

 

2020-08-24