--  ptp  20040205  Find the number of @pcUnits between two datetime values

CREATE FUNCTION dbo.fTimeBetween(
   @pcUnit		VARCHAR(10)
,  @pdBegin		DATETIME
,  @pdEnd		DATETIME
)  RETURNS INT
AS BEGIN

DECLARE
   @iGuess		INT
,  @dTest		DATETIME

IF @pcUnit LIKE '%d%'
   BEGIN
      SELECT @iGuess = DateDiff(day, @pdBegin, @pdEnd)
      SELECT @dTest = DateAdd(day, @iGuess, @pdBegin)
   END
ELSE IF @pcUnit LIKE '%m%'
   BEGIN
      SELECT @iGuess = DateDiff(month, @pdBegin, @pdEnd)
      SELECT @dTest = DateAdd(month, @iGuess, @pdBegin)
   END
ELSE IF @pcUnit LIKE '%h%'
   BEGIN
      SELECT @iGuess = DateDiff(hour, @pdBegin, @pdEnd)
      SELECT @dTest = DateAdd(hour, @iGuess, @pdBegin)
   END
ELSE
   BEGIN
      SELECT @iGuess = DateDiff(year, @pdBegin, @pdEnd)
      SELECT @dTest = DateAdd(year, @iGuess, @pdBegin)
   END


RETURN CASE WHEN @pdEnd < @dTest THEN @iGuess - 1 ELSE @iGuess END
END
GO

SELECT fTimeBetween('Year', Convert(datetime, Str(y, 4) + '-' + Str(m, 2) + '-' + Str(d, 2), GetDate())

quote:

---

Originally posted by Pat Phelan

That should get you lots closer, but it doesn't like leap years.

---