Article: DATEDIFF Function Demystified

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"

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

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

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 Larsson
Helsingborg, 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 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

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"

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 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"

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"

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.

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/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

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"

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"

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.

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

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 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"

Krull
Starting Member

6 Posts

Posted - 2009-01-23 : 08:34:16

Thanks Peso! This looks like it's working perfectly.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources