| Author |
 Topic  |
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
 Posted - 01/23/2009 : 11:48:18
|
And still possible to use as an inline function too!
E 12°55'05.63"
N 56°04'39.26" |
 |
|
|
heynorris
Starting Member
USA
2 Posts |
Posted - 02/12/2009 : 17:19:07
|
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... |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 02/13/2009 : 03:14:24
|
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" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 02/13/2009 : 03:44:13
|
But if you meant this scenarioSELECT dbo.fnMonthsApart('20090128', '20090228') -- 1
SELECT dbo.fnMonthsApart('20090129', '20090228') -- 0
SELECT dbo.fnMonthsApart('20090130', '20090228') -- 0
SELECT dbo.fnMonthsApart('20090131', '20090228') -- 0where 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" |
|
|
|
heynorris
Starting Member
USA
2 Posts |
Posted - 02/13/2009 : 16:24:13
|
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... |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
|
|
Taxeon
Starting Member
1 Posts |
Posted - 03/02/2010 : 13:38:28
|
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 |
|
|
|
eljapo4
Yak Posting Veteran
United Kingdom
94 Posts |
Posted - 09/07/2010 : 11:21:32
|
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! |
|
|
|
Topic |
|
|
|
Article: DATEDIFF Function Demystified
