Date difference in SQL Server 2005

Question

I have two dates of the form Start Date: 2007-03-24, End Date: 2009-06-26

Now I need to find the difference between these two in the below form:

2 years, 3 months and 2 days

Can someone please … in SQL Server 2005

Answer 1

try this:

DECLARE @StartDate datetime
       ,@EndDate   datetime

SELECT  @StartDate ='2007-03-24'
       ,@EndDate   ='2009-06-26'


SELECT
    STUFF ( 
              (CASE
                   WHEN DATEDIFF(year,@StartDate,@EndDate)>1 THEN ', '+CONVERT(varchar(5),DATEDIFF(year,@StartDate,@EndDate))+' years'
                   WHEN DATEDIFF(year,@StartDate,@EndDate)=1 THEN ', '+CONVERT(varchar(5),DATEDIFF(year,@StartDate,@EndDate))+' year'
                   ELSE ''
               END
               +CASE
                    WHEN DATEDIFF(month,@StartDate,@EndDate)-(DATEDIFF(year,@StartDate,@EndDate)*12)>1 THEN ', '+CONVERT(varchar(5),DATEDIFF(month,@StartDate,@EndDate)-(DATEDIFF(year,@StartDate,@EndDate)*12))+' months'
                    WHEN DATEDIFF(month,@StartDate,@EndDate)-(DATEDIFF(year,@StartDate,@EndDate)*12)=1 THEN ', '+CONVERT(varchar(5),DATEDIFF(month,@StartDate,@EndDate)-(DATEDIFF(year,@StartDate,@EndDate)*12))+' month'
                    ELSE ''
                END
               +CASE
                    WHEN DATEDIFF(day,DATEADD(YEAR,DATEDIFF(year,@StartDate,@EndDate),DATEADD(month,DATEDIFF(month,@StartDate,@EndDate)-(DATEDIFF(year,@StartDate,@EndDate)*12),@StartDate)),@EndDate)>1 THEN ', '+CONVERT(varchar(10),DATEDIFF(day,DATEADD(YEAR,DATEDIFF(year,@StartDate,@EndDate),DATEADD(month,DATEDIFF(month,@StartDate,@EndDate)-(DATEDIFF(year,@StartDate,@EndDate)*12),@StartDate)),@EndDate))+' days'
                    WHEN DATEDIFF(day,DATEADD(YEAR,DATEDIFF(year,@StartDate,@EndDate),DATEADD(month,DATEDIFF(month,@StartDate,@EndDate)-(DATEDIFF(year,@StartDate,@EndDate)*12),@StartDate)),@EndDate)>1 THEN ', '+CONVERT(varchar(10),DATEDIFF(day,DATEADD(YEAR,DATEDIFF(year,@StartDate,@EndDate),DATEADD(month,DATEDIFF(month,@StartDate,@EndDate)-(DATEDIFF(year,@StartDate,@EndDate)*12),@StartDate)),@EndDate))+' day'
                    ELSE ''
                END
              )
              , 1, 2, ''
          )

OUTPUT:

-------------------------------------------
2 years, 3 months, 2 days

(1 row(s) affected)

set based sample:

DECLARE @YourTable table (RowID int, StartDate datetime, EndDate datetime)
SET NOCOUNT ON
INSERT @YourTable VALUES ( 1,'2007-03-24','2009-06-26')
INSERT @YourTable VALUES ( 2,'2008-03-24','2009-06-26')
INSERT @YourTable VALUES ( 3,'2009-03-24','2009-06-26')
INSERT @YourTable VALUES ( 4,'2009-04-24','2009-06-26')
INSERT @YourTable VALUES ( 5,'2009-05-24','2009-06-26')
INSERT @YourTable VALUES ( 6,'2009-06-24','2009-06-26')
INSERT @YourTable VALUES ( 7,'2009-06-25','2009-06-26')
INSERT @YourTable VALUES ( 8,'2009-06-26','2009-06-26')
INSERT @YourTable VALUES ( 9,'2009-06-26 5:00','2009-06-26 23:00')
INSERT @YourTable VALUES (10,'2009-06-26 5:00','2009-06-26 6:00')
INSERT @YourTable VALUES (11,'2007-06-24','2009-06-24')
INSERT @YourTable VALUES (12,'2009-03-24','2009-06-24')
INSERT @YourTable VALUES (13,'2007-03-24','2009-06-24')
SET NOCOUNT OFF


SELECT RowID,
    ISNULL(dt.YearOf+CASE
                         WHEN dt.MonthOf IS NOT NULL AND dt.DayOf IS NOT NULL THEN ', '
                         WHEN dt.MonthOf IS NOT NULL THEN ' and '
                         ELSE ''
                     END
                    ,''
          )
        +ISNULL(dt.MonthOf+CASE
                               WHEN dt.DayOf IS NOT NULL THEN ' and '
                               ELSE ''
                           END
                         ,''
               )
        +ISNULL(dt.DayOf,'') AS DifferenceOf
    FROM (SELECT
              RowId
                  ,CASE
                       WHEN DATEDIFF(year,StartDate,EndDate)>1 THEN CONVERT(varchar(5),DATEDIFF(year,StartDate,EndDate))+' years'
                       WHEN DATEDIFF(year,StartDate,EndDate)=1 THEN CONVERT(varchar(5),DATEDIFF(year,StartDate,EndDate))+' year'
                       ELSE null
                   END AS YearOf
                  ,CASE
                       WHEN DATEDIFF(month,StartDate,EndDate)-(DATEDIFF(year,StartDate,EndDate)*12)>1 THEN CONVERT(varchar(5),DATEDIFF(month,StartDate,EndDate)-(DATEDIFF(year,StartDate,EndDate)*12))+' months'
                       WHEN DATEDIFF(month,StartDate,EndDate)-(DATEDIFF(year,StartDate,EndDate)*12)=1 THEN CONVERT(varchar(5),DATEDIFF(month,StartDate,EndDate)-(DATEDIFF(year,StartDate,EndDate)*12))+' month'
                       ELSE null
                   END AS MonthOf
                  ,CASE
                       WHEN DATEDIFF(day,DATEADD(YEAR,DATEDIFF(year,StartDate,EndDate),DATEADD(month,DATEDIFF(month,StartDate,EndDate)-(DATEDIFF(year,StartDate,EndDate)*12),StartDate)),EndDate)>1 THEN CONVERT(varchar(10),DATEDIFF(day,DATEADD(YEAR,DATEDIFF(year,StartDate,EndDate),DATEADD(month,DATEDIFF(month,StartDate,EndDate)-(DATEDIFF(year,StartDate,EndDate)*12),StartDate)),EndDate))+' days'
                       WHEN DATEDIFF(day,DATEADD(YEAR,DATEDIFF(year,StartDate,EndDate),DATEADD(month,DATEDIFF(month,StartDate,EndDate)-(DATEDIFF(year,StartDate,EndDate)*12),StartDate)),EndDate)>1 THEN CONVERT(varchar(10),DATEDIFF(day,DATEADD(YEAR,DATEDIFF(year,StartDate,EndDate),DATEADD(month,DATEDIFF(month,StartDate,EndDate)-(DATEDIFF(year,StartDate,EndDate)*12),StartDate)),EndDate))+' day'
                       WHEN DATEDIFF(minute,StartDate,EndDate)<1 THEN 'no difference'
                       WHEN DATEDIFF(hour,StartDate,EndDate)<12 THEN 'less than a day'
                       WHEN DATEDIFF(hour,StartDate,EndDate)<=24 THEN '1 day'
                       ELSE null
                   END AS DayOf
              FROM @YourTable
         ) dt

OUTPUT:

RowID       DifferenceOf
----------- ------------------------------------------------
1           2 years, 3 months and 2 days
2           1 year, 3 months and 2 days
3           3 months and 2 days
4           2 months and 2 days
5           1 month and 2 days
6           2 days
7           1 day
8           no difference
9           1 day
10          less than a day
11          2 years
12          3 months
13          2 years and 3 months

(13 row(s) affected)