jeudi 29 janvier 2009

DAYS360 in SQL Server

Ok, so this function is quite interesting in Excel but doesn't exists in SQL Server.
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 !

1 commentaire:

Unknown a dit…

THANK YOU SO MUCH