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
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

SwePeso
Patron Saint of Lost Yaks

Sweden
29908 Posts

Posted - 01/23/2009 :  11:48:18  Show Profile  Visit SwePeso's Homepage  Reply with Quote
And still possible to use as an inline function too!



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

heynorris
Starting Member

USA
2 Posts

Posted - 02/12/2009 :  17:19:07  Show Profile  Reply with Quote
Peso,

I really appreciate the functions, but I did manage to find a problem with fnMonthsApart. Consider:

If the @FromDate is 2/28/2009 (the last day of Feb'09) while @ToDate is 3/28/2008 (not the last day of Mar'09), then the function will incorrectly say that 1 full month has passed. In actuality, a common interpretation for most folks (I think) is that a full month has not passed in that case.

This problem will occur any time @FromDate is the last day of a month that has less than 31 days (ie, Feb 28/29th or Apr/Jun/Sep/Nov 30th) while @ToDate is the same numeric day value but not the last day of a longer month. (ie, From=4/30/2009 and To=5/30/2009).

The original approach in the function is to use the DAY-value of the month in the From and To dates to determine when to use MSSQL's DATEDIFF function as-is or subtract 1 from its result. Instead, I suggest calculating how far each date is from the end of its month and use that value to compare the From and To dates. This way, 2/28/2009 can be a last day of month but 3/28/2009 will be seen as 2 days from the last day of the month, thus indicating a whole month has not passed.

Here's my code for fnMonthsApart:

CREATE FUNCTION dbo.fnMonthsApart
(	@FromDate DATETIME,
	@ToDate DATETIME
)
RETURNS INT
AS
BEGIN
	RETURN
		CASE
			WHEN @FromDate > @ToDate 
				THEN NULL
			WHEN DATEDIFF(dd, @FromDate, DATEADD(dd,-1,CAST((MONTH(@FromDate)+1) as VARCHAR(2)) + '/1/' + CAST(YEAR(@FromDate) as VARCHAR(4))))
				< DATEDIFF(dd, @ToDate, DATEADD(dd,-1,CAST((MONTH(@ToDate)+1) as VARCHAR(2)) + '/1/' + CAST(YEAR(@ToDate) as VARCHAR(4))))
				THEN DATEDIFF(month, @FromDate, @ToDate) - 1
			ELSE
				DATEDIFF(month, @FromDate, @ToDate)
		END
END


I hope this helps someone.

And now, a word from our sponsor...
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29908 Posts

Posted - 02/13/2009 :  03:14:24  Show Profile  Visit SwePeso's Homepage  Reply with Quote
quote:
Originally posted by heynorris

If the @FromDate is 2/28/2009 (the last day of Feb'09) while @ToDate is 3/28/2008 (not the last day of Mar'09), then the function will incorrectly say that 1 full month has passed.
SELECT	dbo.fnMonthsApart('20090228', '20090328')
returns 1.

If you were engaged on Feb 28, 2009 you will be in trouble of you do not send your girl-friend some flowers on your one-month anniversary March 28, 2009.



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

SwePeso
Patron Saint of Lost Yaks

Sweden
29908 Posts

Posted - 02/13/2009 :  03:44:13  Show Profile  Visit SwePeso's Homepage  Reply with Quote
But if you meant this scenario
SELECT	dbo.fnMonthsApart('20090128', '20090228') -- 1
SELECT	dbo.fnMonthsApart('20090129', '20090228') -- 0
SELECT	dbo.fnMonthsApart('20090130', '20090228') -- 0
SELECT	dbo.fnMonthsApart('20090131', '20090228') -- 0
where the three last results according to you should also return 1, use this (all it took was an extra line in the case statemen)
CREATE FUNCTION dbo.fnMonthsApart
(
        @FromDate DATETIME,
        @ToDate DATETIME
)
RETURNS INT
AS
BEGIN
        RETURN  CASE
                       WHEN @FromDate > @ToDate THEN NULL
                       WHEN DATEPART(DAY, @FromDate) > DATEPART(DAY, @ToDate) AND DATEADD(DAY, DATEDIFF(DAY, 0, @ToDate), 0) = DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()), -1) THEN DATEDIFF(MONTH, @FromDate, @ToDate)
                       WHEN DATEPART(DAY, @FromDate) > DATEPART(DAY, @ToDate) THEN DATEDIFF(MONTH, @FromDate, @ToDate) - 1
                       ELSE DATEDIFF(MONTH, @FromDate, @ToDate)
               END
END

CREATE FUNCTION dbo.fnYearsApart
(
        @FromDate DATETIME,
        @ToDate DATETIME
)
RETURNS INT
AS
BEGIN
        RETURN  CASE
                       WHEN @FromDate > @ToDate THEN NULL
                       WHEN DATEPART(DAY, @FromDate) > DATEPART(DAY, @ToDate) AND DATEADD(DAY, DATEDIFF(DAY, 0, @ToDate), 0) = DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()), -1) THEN DATEDIFF(MONTH, @FromDate, @ToDate)
                       WHEN DATEPART(DAY, @FromDate) > DATEPART(DAY, @ToDate) THEN DATEDIFF(MONTH, @FromDate, @ToDate) - 1
                       ELSE DATEDIFF(MONTH, @FromDate, @ToDate)
               END / 12
END



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

heynorris
Starting Member

USA
2 Posts

Posted - 02/13/2009 :  16:24:13  Show Profile  Reply with Quote
I have to admit, I'm not sure how your function reliably gets the last day of a given date's month, but it works for all of my tests. However, I think you inadvertently used "GETDATE()" rather than the @ToDate value.

So here are the corrected versions:


CREATE FUNCTION dbo.fnMonthsApart
(	@FromDate DATETIME,
	@ToDate DATETIME
) RETURNS INT AS
BEGIN
	RETURN	CASE
				WHEN @FromDate > @ToDate 
					THEN NULL
				WHEN DATEPART(DAY, @FromDate) > DATEPART(DAY, @ToDate) 
				AND DATEADD(DAY, DATEDIFF(DAY, 0, @ToDate), 0) <> DATEADD(MONTH, DATEDIFF(MONTH, -1, @ToDate), -1) 
					THEN DATEDIFF(MONTH, @FromDate, @ToDate) - 1
				ELSE
					DATEDIFF(MONTH, @FromDate, @ToDate)
			END
END
GO

CREATE FUNCTION dbo.fnYearsApart
(	@FromDate DATETIME,
	@ToDate DATETIME
) RETURNS INT AS
BEGIN
	RETURN	CASE
				WHEN @FromDate > @ToDate 
					THEN NULL
				WHEN DATEPART(DAY, @FromDate) > DATEPART(DAY, @ToDate) 
				AND DATEADD(DAY, DATEDIFF(DAY, 0, @ToDate), 0) <> DATEADD(MONTH, DATEDIFF(MONTH, -1, @ToDate), -1) 
					THEN DATEDIFF(MONTH, @FromDate, @ToDate) - 1
				ELSE
					DATEDIFF(MONTH, @FromDate, @ToDate)
			END / 12
END
GO


Thanks for your help and input.




And now, a word from our sponsor...
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29908 Posts

Posted - 02/25/2009 :  06:23:36  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Yes. GETDATE() was part of my testbench.
Thank you.

I made a new blog post about the function here
http://weblogs.sqlteam.com/peterl/archive/2009/02/13/Improved-anniversary-calculation-better-datediff.aspx



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

Taxeon
Starting Member

1 Posts

Posted - 03/02/2010 :  13:38:28  Show Profile  Reply with Quote
Here is another somewhat simpler approach that guarantees full periods only are counted.

Just get the number of periods that datediff returns than add those periods to the start period. If your new date exceeds your ToDate than you don't have full periods. Just subtract one from your period result and you are all set. You can easily do this with all periods provided by DateDiff by just providing different periods. This function just goes down to day.

Sadly you can't pass DateDiff's Datepart parameter so you have to pass strings and use a case statement.


CREATE FUNCTION dbo.AAfnBetterDateDiff
(
@psPeriod AS VARCHAR(12)
, @pdtStartDate AS DATETIME
, @pdtEndDate AS DATETIME
)

RETURNS INT
As
BEGIN
DECLARE @iReturn AS INT
DECLARE @iDiffValue AS INT

--First get Diff value this is the number of periods between the two dates that DateDiff Returns

SELECT @iDiffValue =
Case
When lower(@psPeriod) In ('year','yy','yyyy')
THEN DATEDIFF(year,@pdtStartdate, @pdtEndDate)

WHEN LOWER(@psPeriod) In ('quarter','qq','q')
THEN DATEDIFF(quarter,@pdtStartdate, @pdtEndDate)

WHEN LOWER(@psPeriod) In ('month' ,'mm', 'm')
THEN DATEDIFF(month,@pdtStartdate, @pdtEndDate)
WHEN LOWER(@psPeriod) In ('day','dd','d')
THEN DATEDIFF(day,@pdtStartdate, @pdtEndDate)
WHEN LOWER(@psPeriod) In ('week','wk','ww')
THEN DATEDIFF(week,@pdtStartdate, @pdtEndDate)
ELSE NULL
END

IF NOT (@iDiffValue is NULL)

--Now Get our corrected Diff value by testing if the number of periods returned are actually full periods between the dates

Select @iReturn = Case
When lower(@psPeriod) In ('year','yy','yyyy')
THEN CASE WHEN Dateadd(year, @iDiffValue,@pdtStartdate) > @pdtEndDate THEN @iDiffValue-1 ELSE @iDiffValue END
WHEN LOWER(@psPeriod) In ('quarter','qq','q')
THEN CASE WHEN Dateadd(quarter, @iDiffValue,@pdtStartdate) > @pdtEndDate THEN @iDiffValue-1 ELSE @iDiffValue END

WHEN LOWER(@psPeriod) In ('month' ,'mm', 'm')
THEN CASE WHEN Dateadd(month, @iDiffValue,@pdtStartdate) > @pdtEndDate THEN @iDiffValue-1 ELSE @iDiffValue END

WHEN LOWER(@psPeriod) In ('day','dd','d')
THEN CASE WHEN Dateadd(day, @iDiffValue,@pdtStartdate) > @pdtEndDate THEN @iDiffValue-1 ELSE @iDiffValue END

WHEN LOWER(@psPeriod) In ('week','wk','ww')
THEN CASE WHEN Dateadd(week, @iDiffValue,@pdtStartdate) > @pdtEndDate THEN @iDiffValue-1 ELSE @iDiffValue END

END

RETURN @iReturn
END
Go to Top of Page

eljapo4
Yak Posting Veteran

United Kingdom
98 Posts

Posted - 09/07/2010 :  11:21:32  Show Profile  Reply with Quote
Hi guys,

I'm having a bit of trouble with a DateDiff function and I'm hoping you folks might be able to shed some light on the problem here's the code i'm trying to run:
DECLARE
@FromDate DATETIME,
@ToDate DATETIME

SET @FromDate ='2010-06-01 09:00:00'
SET @ToDate ='2010-06-01 10:00:00'

SELECT T0.ObjectID,
T0.POSite AS Site,
T0.PO AS PONumber,
T0.Buyer AS Buyer,
T1.Description AS TradingPartner,
T3.OrdResc AS ResourceCode,
T3.ItemDesc AS ResourceDescription,
T3.OrdQty AS QuantityOrdered,
T3.OrdQtyUM AS UOM,
T3.RecQty AS QuantityReceived,
T3.ApprovDt AS ApprovalDate,
T3.LastRecDt AS ReceiptDate,
T4.CatCodesCode1

FROM poPurchaseOrder T0 (NOLOCK)
INNER JOIN poLine T2 (NOLOCK) ON T2.ParentObjectID = T0.ObjectID
AND T2.ParentClassID = 11588
AND T2.CollectionID = 1
INNER JOIN poDelivery T3 (NOLOCK) ON T3.ParentObjectID = T2.ObjectID
AND T3.ParentClassID = 11608
AND T3.CollectionID = 1
LEFT OUTER JOIN fdTradingPartne T1 (NOLOCK) ON T1.ObjectID = T0.OrderFrTPObjectID
LEFT OUTER JOIN fdBasResc T4 (NOLOCK) ON T4.ObjectID = T2.OrdRescObjectID

WHERE T3.ApprovDt BETWEEN @FromDate AND @ToDate
AND DATEDIFF(hh, T3.LastRecDt, T3.ApprovDt)< 4


the results i'm getting from this query are displaying rows where the date range is a few days old. Does the DATEDIFF function not calculate the days and just look at the timestamp?

Thank you for any help!
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous 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.23 seconds. Powered By: Snitz Forums 2000