SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: DATEDIFF Function Demystified
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 03/20/2007 :  08:55:07  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
5509 Posts

Posted - 03/20/2007 :  09:02:15  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
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

Sweden
30190 Posts

Posted - 03/20/2007 :  09:25:10  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Sweden
30190 Posts

Posted - 03/20/2007 :  17:39:24  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Edited by - SwePeso on 03/21/2007 02:07:12
Go to Top of Page

kbearhome
Starting Member

USA
36 Posts

Posted - 03/23/2007 :  11:23:56  Show Profile  Reply with Quote
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

Sweden
30190 Posts

Posted - 03/24/2007 :  09:52:36  Show Profile  Visit SwePeso's Homepage  Reply with Quote
You're welcome and thanks.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

drewex
Starting Member

USA
4 Posts

Posted - 08/08/2008 :  13:20:24  Show Profile  Reply with Quote
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

Sweden
30190 Posts

Posted - 08/08/2008 :  17:47:10  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"

Edited by - SwePeso on 08/08/2008 17:51:47
Go to Top of Page

Krull
Starting Member

USA
6 Posts

Posted - 01/21/2009 :  13:55:11  Show Profile  Reply with Quote
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

Sweden
30190 Posts

Posted - 01/21/2009 :  16:20:21  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Sweden
30190 Posts

Posted - 01/21/2009 :  16:26:16  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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



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

Krull
Starting Member

USA
6 Posts

Posted - 01/22/2009 :  08:40:44  Show Profile  Reply with Quote
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

Sweden
30190 Posts

Posted - 01/22/2009 :  08:45:58  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

USA
6 Posts

Posted - 01/22/2009 :  09:10:37  Show Profile  Reply with Quote
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

Sweden
30190 Posts

Posted - 01/22/2009 :  09:21:26  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"

Edited by - SwePeso on 01/22/2009 09:40:29
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30190 Posts

Posted - 01/22/2009 :  09:37:19  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

USA
6 Posts

Posted - 01/22/2009 :  09:55:09  Show Profile  Reply with Quote
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

USA
6 Posts

Posted - 01/22/2009 :  11:38:39  Show Profile  Reply with Quote
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

Sweden
30190 Posts

Posted - 01/22/2009 :  13:31:09  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Sweden
30190 Posts

Posted - 01/22/2009 :  13:41:49  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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



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

Krull
Starting Member

USA
6 Posts

Posted - 01/23/2009 :  08:34:16  Show Profile  Reply with Quote
Thanks Peso! This looks like it's working perfectly.
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000