Difference between dates - sql server

Question

I need to get the following result between two dates:

date_start = 01/01/2010
date_end = 10/21/2012

result: 1 year, 9 months and 20 days.

I tried the code bellow, but it didn't work. It returns negative dates sometimes:

SELECT CAST(DATEDIFF(yy, date_start, date_end) AS varchar(4)) +' year '+
       CAST(DATEDIFF(mm, DATEADD(yy, DATEDIFF(yy,date_start , date_end), date_start), date_end) AS varchar(2)) +' month '+
       CAST(DATEDIFF(dd, DATEADD(mm, DATEDIFF(mm, DATEADD(yy, DATEDIFF(yy, date_start, date_end), date_start), date_end), DATEADD(yy, DATEDIFF(yy, date_start, date_end), date_start)), date_end) AS varchar(2)) +' day' AS result

Thank You!

are you sure expected result is 1 year 9months or 2 years 9 months — rs., Sep 25 '12 at 18:15
And what do you want if start date is `2010-01-19` or `2010-01-21`? — Aaron Bertrand, Sep 25 '12 at 18:19
Regardless of how you get to the result, please be careful with what you do with the output. Remember, a month is not a precise unit of time. If your user is aware of one of the points, such as "three months from now" or "three months ago", that is fine. But without a reference point, a "month" is meaningless. — Matt Johnson-Pint, Sep 25 '12 at 19:44

Aaron Bertrand · Accepted Answer · 2012-09-25T18:48:29.103

This may not correctly handle leap years if @s or @e are adjacent to them, but other than that this should be pretty close:

DECLARE @s DATE, @e DATE

SELECT @s = '20100101', @e = '20121021';

SELECT y + ' year(s), ' + m + ' month(s) and ' + d + ' day(s).'
FROM
(
  SELECT 
    RTRIM(y), 
    RTRIM(m - CASE WHEN pd < 0 THEN 1 ELSE 0 END),
    RTRIM(CASE WHEN pd < 0 THEN nd ELSE pd END)
  FROM 
  (
    SELECT
      DATEDIFF(MONTH, @s, @e) / 12, 
      DATEDIFF(MONTH, @s, @e) % 12,
      DATEDIFF(DAY, @s, DATEADD(MONTH, -DATEDIFF(MONTH, @s, @e), @e)),
      DATEDIFF(DAY, @s, DATEADD(MONTH, 1-DATEDIFF(MONTH, @s, @e), @e))
  ) AS x (y, m, pd, nd)
) AS y (y, m, d);

Output:

2 year(s), 9 month(s) and 20 day(s).

If accounting for the extra day in a leap year is crucial, I'm sure it could be adjusted to handle that. Though through minimal testing I wasn't able to see any case where it would break (it just doesn't feel like it should work).

rs. · Answer 2 · 2012-09-25T18:47:49.083

0

Try this

DECLARE @months decimal (5,2)
SELECT @months = DATEDIFF(month, '01/22/2010','10/21/2012')/12.0

;WITH CTE AS 
(
SELECT FLOOR(@months) AS years,(@months-FLOOR(@months)) * 12 AS months
)
SELECT years, CAST(months as int) months, 
case when day('01/22/2010') > day('10/21/2012')  
then day('10/21/2012') +  
datediff(day,'01/22/2010',dateadd(month,datediff(month,0,'01/22/2010')+1,0))- 1
ELSE day('10/21/2012')-day('01/22/2010') end
from cte

edited Sep 25 '12 at 18:47

answered Sep 25 '12 at 18:22

rs.

26,707
12
68
90

2

This doesn't output days (and also won't account for leap years when it does). – Aaron Bertrand Sep 25 '12 at 18:23
Now change the start date to `2010-01-22`... I notice that mine has a similar issue. – Aaron Bertrand Sep 25 '12 at 18:40

score 0 · Answer 3 · answered Jan 21 '14 at 11:05

0

you can get date difference using function also see this http://atikpassion.blogspot.com/2014/01/get-difference-between-two-dates-in.html

answered Jan 21 '14 at 11:05

Mohammad Atiour Islam

5,380
3
43
48

Difference between dates - sql server

3 Answers3

Linked