Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums  Site Related Forums  Article Discussion  Article: DATEDIFF Function Demystified

Author  Topic

0 Posts

 Posted - 2007-03-20 : 08:55:07 This article comes to us from Peter Larsson also known as Peso in the forums. He writes "I have seen many questions about the DATEDIFF function where people are baffled about why it doesn't calculate the correct result. The short answer is 'It does'." Peter discusses the DATEDIFF function and provides two functions that calculate years and months the way you think it should. Thanks Peter!Article Link.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

 Posted - 2007-03-20 : 09:02:15 Excellent Post, Peter. The graphical example really makes it simple to understand the whole concept. I think newbies in datetime handling will find it much useful.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

 Posted - 2007-03-20 : 09:25:10 They have to read it first! That's the first challenge...Thanks.Peter LarssonHelsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

 Posted - 2007-03-20 : 17:39:24 More kinky stuff about DATEDIFF function`SELECT CURRENT_TIMESTAMP AS Now, Status, DATEADD(MONTH, DATEDIFF(MONTH, StartingPoint1, CURRENT_TIMESTAMP), StartingPoint2) AS [MONTH], DATEADD(QUARTER, DATEDIFF(QUARTER, StartingPoint1, CURRENT_TIMESTAMP), StartingPoint2) AS [QUARTER], DATEADD(YEAR, DATEDIFF(YEAR, StartingPoint1, CURRENT_TIMESTAMP), StartingPoint2) AS [YEAR]FROM ( SELECT 'End Previous' AS Status, 0 AS StartingPoint1, -1 AS StartingPoint2 UNION ALL SELECT 'Start Current', 0, 0 UNION ALL SELECT 'End Current', -1, -1 UNION ALL SELECT 'Start Next', -1, 0 ) AS xORDER BY 5`Peter LarssonHelsingborg, Sweden

kbearhome
Starting Member

36 Posts

 Posted - 2007-03-23 : 11:23:56 This was an excellent way of breaking down the calculation to its simplest explanation. Thank you.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

 Posted - 2007-03-24 : 09:52:36 You're welcome and thanks.Peter LarssonHelsingborg, Sweden

drewex
Starting Member

4 Posts

 Posted - 2008-08-08 : 13:20:24 Slight change in your code, added negatif returns instead null`CREATE FUNCTION [dbo].[fnMonthsApart]( @FromDate DATETIME, @ToDate DATETIME)RETURNS INTASBEGIN DECLARE @temp datetime DECLARE @int int IF @FromDate > @ToDate BEGIN SET @int = CASE WHEN DATEPART(day, @FromDate) > DATEPART(day, @ToDate) THEN DATEDIFF(month, @FromDate, @ToDate) - 1 ELSE DATEDIFF(month, @FromDate, @ToDate) END END ELSE BEGIN SET @temp = @ToDate SET @ToDate = @FromDate SET @FromDate = @temp SET @int = -1 * (CASE WHEN DATEPART(day, @FromDate) > DATEPART(day, @ToDate) THEN DATEDIFF(month, @FromDate, @ToDate) - 1 ELSE DATEDIFF(month, @FromDate, @ToDate) END) END RETURN @intEND`

SwePeso
Patron Saint of Lost Yaks

30421 Posts

 Posted - 2008-08-08 : 17:47:10 Why the bloated code? All it takes is this`CREATE FUNCTION dbo.fnMonthsApart( @FromDate DATETIME, @ToDate DATETIME)RETURNS INTASBEGIN RETURN CASE WHEN @FromDate > @ToDate AND DATEPART(day, @ToDate) > DATEPART(day, @FromDate) THEN +1 WHEN @FromDate <= @ToDate AND DATEPART(day, @FromDate) > DATEPART(day, @ToDate) THEN -1 ELSE 0 END + DATEDIFF(month, @FromDate, @ToDate)END`And this`CREATE FUNCTION dbo.fnYearsApart( @FromDate DATETIME, @ToDate DATETIME)RETURNS INTASBEGIN RETURN (CASE WHEN @FromDate > @ToDate AND DATEPART(day, @ToDate) > DATEPART(day, @FromDate) THEN +1 WHEN @FromDate <= @ToDate AND DATEPART(day, @FromDate) > DATEPART(day, @ToDate) THEN -1 ELSE 0 END + DATEDIFF(month, @FromDate, @ToDate)) / 12END`E 12°55'05.25"N 56°04'39.16"

Krull
Starting Member

6 Posts

 Posted - 2009-01-21 : 13:55:11 I have to say this was a very helpful article, I could not figure out why my calculations were wrong sometimes and this almost solved my problem.I've spent a few hours trying to adjust this to return results for my particular issue. I need to include all days in the ToDay month up to the same day of week -1 from FromDate.Example: 3/1/2009 is a Sunday, my EndDate is 4/4/2009, as Saturday. This is over a month but for my purpose it would not be considered a month until 4/5/2009 (The 1st Sunday after EndDate)I've tried all kinds of calculations using DatePart, DayOfWeek and so on but with no luck. Any help would be greatly appreciated.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

 Posted - 2009-01-21 : 16:20:21 Using the function fnMonthsApart in the article gives you the correct result`DECLARE @FromDate DATETIME, @ToDate DATETIMESELECT @FromDate = '2009-03-01', -- March 1st 2009 @ToDate = '2009-04-04' -- April 4th 2009SELECT CASE WHEN @FromDate > @ToDate THEN NULL WHEN DATEPART(day, @FromDate) > DATEPART(day, @ToDate) THEN DATEDIFF(month, @FromDate, @ToDate) - 1 ELSE DATEDIFF(month, @FromDate, @ToDate) END`Result is 1.E 12°55'05.63"N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

 Posted - 2009-01-21 : 16:26:16 [code]DECLARE @FromDate DATETIME, @ToDate DATETIMESELECT @FromDate = '2009-03-01', -- March 1st 2009, sunday @ToDate = '2009-04-04' -- April 4th 2009, saturday-- To deal with this particular problem, get following sunday if not already sunday for @ToDateSET @ToDate = DATEADD(DAY, DATEDIFF(DAY, '1753-01-01', @ToDate) / 7 * 7, '1753-01-07')-- Display resultSELECT @FromDate AS OriginalFromDate, @ToDate AS NewToDate[/code]E 12°55'05.63"N 56°04'39.26"

Krull
Starting Member

6 Posts

 Posted - 2009-01-22 : 08:40:44 quote:Originally posted by PesoUsing the function fnMonthsApart in the article gives you the correct result`DECLARE @FromDate DATETIME, @ToDate DATETIMESELECT @FromDate = '2009-03-01', -- March 1st 2009 @ToDate = '2009-04-04' -- April 4th 2009SELECT CASE WHEN @FromDate > @ToDate THEN NULL WHEN DATEPART(day, @FromDate) > DATEPART(day, @ToDate) THEN DATEDIFF(month, @FromDate, @ToDate) - 1 ELSE DATEDIFF(month, @FromDate, @ToDate) END`Result is 1.E 12°55'05.63"N 56°04'39.26"I agree it is a correct result, however for my needs it should return 0 unless its that next sunday(4/5/2009). I've been trying to adjust the enddate so as to trick the function but I think it would be best to actually have the function do the adjustment so it could be re-used in some other projects.I hope that makes sence.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

 Posted - 2009-01-22 : 08:45:58 Give me some more sample data ranges and expected "month" count.E 12°55'05.63"N 56°04'39.26"

Krull
Starting Member

6 Posts

 Posted - 2009-01-22 : 09:10:37 Start 1/3/2010End 1/6/2010Returns 0Start 1/3/2010end 3/6/2010Returns 1Start 1/3/2010End 3/7/2010Returns 2Start 6/6/2010End 7/1 thru 3/2010Returns 0Start 6/6/2010End 7/4/2010Returns 1Start 1/3/2010End 12/4/2010Returns 10Start 1/3/2010End 12/5/2010Returns 11

SwePeso
Patron Saint of Lost Yaks

30421 Posts

 Posted - 2009-01-22 : 09:21:26 Start 6/6/2010End 7/1 thru 3/2010 -- Should be 7 to 10 JulyReturns 0Start 6/6/2010End 7/4/2010 -- Should be 11 JulyReturns 1E 12°55'05.63"N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

 Posted - 2009-01-22 : 09:37:19 This should do it if I understand you correctly`DECLARE @FromDate DATETIME, @ToDate DATETIMESELECT @FromDate = '2009-03-01', @ToDate = '2009-04-04'-- Display the month count according to business rules, not calendarSELECT MAX(mc) AS MonthCountFROM ( SELECT 0 AS mc, @FromDate AS dt UNION ALL SELECT DATEDIFF(MONTH, @FromDate, @ToDate) - 1, DATEADD(DAY, DATEDIFF(DAY, @FromDate, DATEADD(MONTH, DATEDIFF(MONTH, @FromDate, @ToDate) - 1, @FromDate)) / 7 * 7 + 7, @FromDate) UNION ALL SELECT DATEDIFF(MONTH, @FromDate, @ToDate), DATEADD(DAY, DATEDIFF(DAY, @FromDate, DATEADD(MONTH, DATEDIFF(MONTH, @FromDate, @ToDate), @FromDate)) / 7 * 7 + 7, @FromDate) ) AS dWHERE @ToDate >= dt`E 12°55'05.63"N 56°04'39.26"

Krull
Starting Member

6 Posts

 Posted - 2009-01-22 : 09:55:09 Actually the 1st date would be correct.Start 6/6/2010 - 1st SundayEnd 7/1 thru 3/2010 -- 1st Week 7/4/2010 - 1st Sunday Would return 1Returns 0It looks like the problem starts when the StartDate DayOfMonth(6) is higher than the EndDate DayOfMonth(3). July 11th would be the 2nd Sunday, July 4th would be the 1st Sunday as the Start Day of June 6th 2010 is the 1st Sunday as well.I apoligize for the confusion.

Krull
Starting Member

6 Posts

 Posted - 2009-01-22 : 11:38:39 Does this look reasonable, minor adjustment? I ran it though a bunch of dates and had positive results.`DECLARE @FromDate DATETIME, @ToDate DATETIMEDECLARE @Calculation intSELECT @FromDate = '2010-6-6', @ToDate = '2010-7-4'IF Day(@FromDate) >= Day(@ToDate) SET @Calculation = 0ELSE SET @Calculation = 7-- Display the month count according to business rules, not calendar SELECT MAX(mc) AS MonthCountFROM ( SELECT 0 AS mc, @FromDate AS dt UNION ALL SELECT DATEDIFF(MONTH, @FromDate, @ToDate) - 1, DATEADD(DAY, DATEDIFF(DAY, @FromDate, DATEADD(MONTH, DATEDIFF(MONTH, @FromDate, @ToDate) - 1, @FromDate)) / 7 * 7 + @Calculation , @FromDate) UNION ALL SELECT DATEDIFF(MONTH, @FromDate, @ToDate), DATEADD(DAY, DATEDIFF(DAY, @FromDate, DATEADD(MONTH, DATEDIFF(MONTH, @FromDate, @ToDate), @FromDate)) / 7 * 7 + @Calculation, @FromDate) ) AS dWHERE @ToDate >= dt`

SwePeso
Patron Saint of Lost Yaks

30421 Posts

 Posted - 2009-01-22 : 13:31:09 As long as it work, use it.E 12°55'05.63"N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

 Posted - 2009-01-22 : 13:41:49 [code]DECLARE @FromDate DATETIME, @ToDate DATETIMESELECT @FromDate = '2010-6-6', @ToDate = '2010-7-4'-- Display the month count according to business rules, not calendar SELECT MAX(mc) AS MonthCountFROM ( SELECT 0 AS mc, @FromDate AS dt UNION ALL SELECT DATEDIFF(MONTH, @FromDate, @ToDate) - 1, DATEADD(DAY, DATEDIFF(DAY, @FromDate, DATEADD(MONTH, DATEDIFF(MONTH, @FromDate, @ToDate) - 1, @FromDate)) / 7 * 7, @FromDate) WHERE Day(@FromDate) >= Day(@ToDate) UNION ALL SELECT DATEDIFF(MONTH, @FromDate, @ToDate) - 1, DATEADD(DAY, DATEDIFF(DAY, @FromDate, DATEADD(MONTH, DATEDIFF(MONTH, @FromDate, @ToDate) - 1, @FromDate)) / 7 * 7 + 7, @FromDate) WHERE Day(@FromDate) < Day(@ToDate) UNION ALL SELECT DATEDIFF(MONTH, @FromDate, @ToDate), DATEADD(DAY, DATEDIFF(DAY, @FromDate, DATEADD(MONTH, DATEDIFF(MONTH, @FromDate, @ToDate), @FromDate)) / 7 * 7, @FromDate) WHERE Day(@FromDate) >= Day(@ToDate) UNION ALL SELECT DATEDIFF(MONTH, @FromDate, @ToDate), DATEADD(DAY, DATEDIFF(DAY, @FromDate, DATEADD(MONTH, DATEDIFF(MONTH, @FromDate, @ToDate), @FromDate)) / 7 * 7 + 7, @FromDate) WHERE Day(@FromDate) < Day(@ToDate) ) AS dWHERE @ToDate >= dt[/code]E 12°55'05.63"N 56°04'39.26"

Krull
Starting Member

6 Posts

 Posted - 2009-01-23 : 08:34:16 Thanks Peso! This looks like it's working perfectly.
Previous Page&nsp;