Thursday, March 1

Complete years between 2 dates

A problem we have had calculating ages was due to it using the SQL Server "DateDiff" function. This counts the number of times a day crosses a boundary, e.g.

DateDiff(year,'12/31/2006','1/1/2007') is 1, as it crosses the 2006-2007 boundary.
DateDiff(year,'1/1/2006','12/31/2006') is 0 as it doesn't cross a year boundary.

I have written a function "yeardiff", which calculates the number of complete years between 2 dates, so

YearDiff('12/31/2006','1/1/2007') is 0
YearDiff('1/1/2006','12/31/2006') is 0
YearDiff('12/31/2006','1/1/2008') is 1

This function works by calculating the number of days the start date is into the year, subtracting this from both start and end dates (so the start date is 1 January), and calling DateDiff on the resulting date.

Here's the source code for this function:


CREATE FUNCTION yeardiff
(@start as datetime, @end as datetime)
returns int
AS
BEGIN
declare @years as int

declare @daysintoyear as int
declare @firstdayofyear as datetime
declare @newstart as datetime
declare @newend as datetime

set @firstdayofyear = convert(datetime,
'1/1/'+convert(varchar,year(@start)))
set @daysintoyear =datediff(day, @start, @firstdayofyear)

set @newstart = dateadd(dd, @daysintoyear,@start)
set @newend = dateadd(dd, @daysintoyear,@end)

return datediff(year,@newstart,@newend)

END

No comments: