I couldn't find one quickly on the web so I did it myself.
What's the goal ?
Determine the number of days covering a period. This number is often used for accounting because it's based on a year of 360 days and on months of 30 days.
Ex : 2007/11/01 to 2009/04/18 = 527 in DAYS360 but 534 "real" days.
And this method works the same way for any period :
days360(2007/01/01,2007/03/01) = days360(2008/01/01,2008/03/01).
Here is my solution :
DROP FUNCTION DAYS360
GO
CREATE FUNCTION DAYS360(@DATE1 DATETIME,@DATE2 DATETIME,@METHOD BIT = 0)
RETURNS INT
AS
BEGIN
DECLARE @dFrom DATETIME,@dTo DATETIME
DECLARE @y INT,@m INT,@d INT,@totalDays INT
SELECT @y=0,@m=0,@d=0
--prepare date that will be used. the smallest goes to @dFrom & the biggest to @dTo
IF @DATE1<@DATE2
SELECT @dFrom=@DATE1,@dTo=@DATE2
ELSE
SELECT @dFrom=@DATE2,@dTo=@DATE1
IF @METHOD=1 -- EU MODE : Cfr Excel help
BEGIN
IF day(@dFrom)=31 SET @dFrom = dateadd(d,-1,@dFrom)
IF day(@dTo)=31 SET @ dTo = dateadd(d,-1,@ dTo)
END
--Get year
SET @y = datediff(yy,@dFrom,@dTo)
--Get month
SET @m = month(@dTo)
--if month from biggest is smaller than month from smallest, we add 12months and remove 1 year
IF @m<month(@dFrom)
BEGIN
SET @y=@y-1
SET @m=@m+12
END
SET @m=@m - month(@dFrom)
--Get day
SET @d=day(@dTo)
--if day from biggest is smaller than day from smallest, we add 30 days and remove 1 month.
--(We remove One year if the value for months was 0)
IF @d<day(@dFrom)
BEGIN
IF @m=0
BEGIN
SET @y=@y-1
SET @m=@m+12
END
SET @m=@m-1
SET @d=@d+30
END
SET @d=@d - day(@dFrom)
--Compute total days
SET @totalDays=(@Y*360)+(@m*30)+@d
RETURN @totalDays
END
Call this with :
SELECT dbo.days360('20070101','20070301',DEFAULT)
And it returns 60 !