UDF to return Years, Months & Days between 2 dates
There are two UDF's included here.
The first one will return the number of Years, Months and Days between a Start and End date.
The second one will return either Years, Months or Days which makes it much easier to include in a select.
If you know of or find a better/faster way to do this, then please post it.
/*
Returns the number of years, months and days between @startdate and @enddate in a single call but as a table
*/
CREATE FUNCTION udfElapsedDate (
@startdate SMALLDATETIME,
@enddate SMALLDATETIME
)
RETURNS @retElapsed TABLE (Years SMALLINT, Months TINYINT, Days TINYINT) AS
BEGIN
DECLARE @smonth TINYINT, @sday TINYINT, @syear SMALLINT
DECLARE @emonth TINYINT, @eday TINYINT, @eyear SMALLINT
DECLARE @months TINYINT, @days TINYINT, @years SMALLINT
DECLARE @tdate SMALLDATETIME
SET @smonth = MONTH(@startdate)
SET @sday = DAY(@startdate)
SET @syear = YEAR(@startdate)
SET @emonth = MONTH(@enddate)
SET @eday = DAY(@enddate)
SET @eyear = YEAR(@enddate)
SET @years = @eyear - @syear
SET @months = 0
SET @days = 0
IF (@emonth >= @smonth)
SET @months = @emonth - @smonth
ELSE
BEGIN
SET @years = @years - 1
SET @months = @emonth + 12 - @smonth
END
IF (@eday >= @sday)
SET @days = @eday - @sday
ELSE
BEGIN
IF (@months > 0)
SET @months = @months - 1
ELSE
BEGIN
SET @years = @years - 1
SET @months = @months + 11
END
SET @tdate = DATEADD(yy,@years,@startdate)
SET @tdate = DATEADD(m,@months,@tdate)
SET @days = DATEDIFF(d,@tdate,@enddate)
END
INSERT @retElapsed SELECT @years, @months, @days
RETURN
END
/*
Returns the number of years, months and days between @startdate and @enddate but only one for each of Y,M,D
Returns as an INT so can be used in a SELECT statement
*/
CREATE FUNCTION udfElapsedDatePart (
@startdate SMALLDATETIME,
@enddate SMALLDATETIME,
@period CHAR(1) -- Must be 'Y', 'M' or 'D'
)
RETURNS INT AS
BEGIN
DECLARE @smonth TINYINT, @sday TINYINT, @syear SMALLINT
DECLARE @emonth TINYINT, @eday TINYINT, @eyear SMALLINT
DECLARE @months TINYINT, @days TINYINT, @years SMALLINT
DECLARE @tdate SMALLDATETIME, @ret INT
SET @smonth = MONTH(@startdate)
SET @sday = DAY(@startdate)
SET @syear = YEAR(@startdate)
SET @emonth = MONTH(@enddate)
SET @eday = DAY(@enddate)
SET @eyear = YEAR(@enddate)
SET @years = @eyear - @syear
SET @months = 0
SET @days = 0
IF (@emonth >= @smonth)
SET @months = @emonth - @smonth
ELSE
BEGIN
SET @years = @years - 1
SET @months = @emonth + 12 - @smonth
END
IF (@eday >= @sday)
SET @days = @eday - @sday
ELSE
BEGIN
IF (@months > 0)
SET @months = @months - 1
ELSE
BEGIN
SET @years = @years - 1
SET @months = @months + 11
END
SET @tdate = DATEADD(yy,@years,@startdate)
SET @tdate = DATEADD(m,@months,@tdate)
SET @days = DATEDIFF(d,@tdate,@enddate)
END
IF @period = 'Y'
SET @ret = @years
IF @period = 'M'
SET @ret = @months
IF @period = 'D'
SET @ret = @days
RETURN @ret
END