Date difference in SQL Server 2005

I have two dates of the form Start Date: 2007-03-24, End D开发者_Go百科ate: 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

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)

I believe the datediff function will get you some of the way, but it is probably better to get the application to do this more detailed formatting. Stackoverflow gives an approximation of when a question was submitted (eg 1 year ago), not an exact indicator (eg 1 year, 3 days ago)

Datediff: http://msdn.microsoft.com/en-us/library/aa258269(SQL.80).aspx

You can chunk the calculation:

• Start with the years (i.e. get the difference in years) and record this
• Add the years to one of the dates (dateadd yy . . .)
• Calculate the difference in months between your year-adjusted date and the other date and record this.
• Add the months to the same date you added the years to
• Calculate the days difference.

Try this:

DECLARE @StartDate DATETIME 
SET @StartDate = '2007-03-24'

DECLARE @EndDate DATETIME 
SET @EndDate = '2009-06-26'

DECLARE @Years INT

SELECT
    @Years = DATEDIFF(YEAR, @StartDate, @enddate)

DECLARE @Months INT

SELECT
    @Months = DATEDIFF(MONTH, DATEADD(YEAR, @Years, @StartDate), @enddate)

DECLARE @Days INT

SELECT
    @Days = DATEDIFF(DAY, DATEADD(MONTH, @Months, DATEADD(YEAR, @Years, @StartDate)), @enddate) 

DECLARE @Message VARCHAR(200)

SET @Message = 'The date difference is ' + CAST(@Years AS VARCHAR(10)) + ' years, ' 
                + CAST(@months AS VARCHAR(10)) + ' month and ' + CAST(@days AS VARCHAR(10)) + ' days'

SELECT @Message

Gives you an output of:

The date difference is 2 years, 3 month and 2 days