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 

AskSQLTeam
Ask SQLTeam Question

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 Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

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 Larsson
Helsingborg, Sweden
Go to Top of Page

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 x
ORDER BY 5

Peter Larsson
Helsingborg, Sweden
Go to Top of Page

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.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

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


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

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 INT
AS
BEGIN
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 @int
END
Go to Top of Page

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 INT
AS
BEGIN
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 INT
AS
BEGIN
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)) / 12
END


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

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.
Go to Top of Page

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 DATETIME

SELECT @FromDate = '2009-03-01', -- March 1st 2009
@ToDate = '2009-04-04' -- April 4th 2009

SELECT 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"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-21 : 16:26:16
[code]DECLARE @FromDate DATETIME,
@ToDate DATETIME

SELECT @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 @ToDate
SET @ToDate = DATEADD(DAY, DATEDIFF(DAY, '1753-01-01', @ToDate) / 7 * 7, '1753-01-07')

-- Display result
SELECT @FromDate AS OriginalFromDate,
@ToDate AS NewToDate[/code]


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

Krull
Starting Member

6 Posts

Posted - 2009-01-22 : 08:40:44
quote:
Originally posted by Peso

Using the function fnMonthsApart in the article gives you the correct result
DECLARE	@FromDate DATETIME,
@ToDate DATETIME

SELECT @FromDate = '2009-03-01', -- March 1st 2009
@ToDate = '2009-04-04' -- April 4th 2009

SELECT 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.
Go to Top of Page

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"
Go to Top of Page

Krull
Starting Member

6 Posts

Posted - 2009-01-22 : 09:10:37
Start 1/3/2010
End 1/6/2010
Returns 0

Start 1/3/2010
end 3/6/2010
Returns 1

Start 1/3/2010
End 3/7/2010
Returns 2

Start 6/6/2010
End 7/1 thru 3/2010
Returns 0

Start 6/6/2010
End 7/4/2010
Returns 1

Start 1/3/2010
End 12/4/2010
Returns 10

Start 1/3/2010
End 12/5/2010
Returns 11
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-22 : 09:21:26
Start 6/6/2010
End 7/1 thru 3/2010 -- Should be 7 to 10 July
Returns 0

Start 6/6/2010
End 7/4/2010 -- Should be 11 July

Returns 1


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

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 DATETIME

SELECT @FromDate = '2009-03-01',
@ToDate = '2009-04-04'

-- Display the month count according to business rules, not calendar
SELECT MAX(mc) AS MonthCount
FROM (
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 d
WHERE @ToDate >= dt



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

Krull
Starting Member

6 Posts

Posted - 2009-01-22 : 09:55:09
Actually the 1st date would be correct.

Start 6/6/2010 - 1st Sunday
End 7/1 thru 3/2010 -- 1st Week 7/4/2010 - 1st Sunday Would return 1
Returns 0

It 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.
Go to Top of Page

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 DATETIME
DECLARE @Calculation int

SELECT @FromDate = '2010-6-6',
@ToDate = '2010-7-4'

IF Day(@FromDate) >= Day(@ToDate)
SET @Calculation = 0
ELSE
SET @Calculation = 7
-- Display the month count according to business rules, not calendar

SELECT MAX(mc) AS MonthCount
FROM (
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 d
WHERE @ToDate >= dt
Go to Top of Page

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"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-22 : 13:41:49
[code]DECLARE @FromDate DATETIME,
@ToDate DATETIME

SELECT @FromDate = '2010-6-6',
@ToDate = '2010-7-4'

-- Display the month count according to business rules, not calendar

SELECT MAX(mc) AS MonthCount
FROM (
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 d
WHERE @ToDate >= dt[/code]


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

Krull
Starting Member

6 Posts

Posted - 2009-01-23 : 08:34:16
Thanks Peso! This looks like it's working perfectly.
Go to Top of Page
    Next Page

- Advertisement -