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

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.

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

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

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

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?