All geek questions in one place

Date Difference in SQL Server 2005

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

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

2 years, 3 months and 2 days

Can someone please ... in SQL Server 2005

+3
source share
4 answers

try the following:

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, ''
          )

CONCLUSION:

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

(1 row(s) affected)

preset 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)
+1
source

I suppose the lateiff function will give you part of the path, but it's probably best to get the application to do this in more detail formatting. Stackoverflow gives an approximate value when the question was sent (e.g., a year ago), and not an accurate indicator (e.g., 1 year, 3 days ago)

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

0

:

  • (.. ) .
  • (dateadd yy.).
  • Calculate the difference in months between your due date and another date and write it down.
  • Add months to the same date you added years to
  • Calculate the difference in days.
0
source

Try the following:

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 output:

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

Source: https://habr.com/ru/post/1746849/

More articles:


All Articles