# Thinking outside the box

Patron Saint of Lost Yaks

## Excel DAYS360 clone

CREATE FUNCTION dbo.DATEDIFF360
(
@source DATETIME,
@target DATETIME,
@style BIT = 0
)
RETURNS INT
AS
BEGIN
RETURN  CASE @style
-- European style
WHEN 1 THEN CASE
WHEN DATEPART(DAY, @target) = 31 THEN 30
ELSE DATEPART(DAY, @target)
END
- CASE
WHEN DATEPART(DAY, @source) = 31 THEN 30
ELSE DATEPART(DAY, @source)
END
-- US style
ELSE CASE
WHEN DATEPART(DAY, @target) = 31 AND DATEPART(DAYOFYEAR, @source) = 60 AND DATEPART(MONTH, @source) = 2 THEN 30
WHEN DATEPART(DAY, @target) = 31 AND DATEPART(DAY, @source) < 30 THEN 31
WHEN DATEPART(DAY, @target) = 31 THEN 30
ELSE DATEPART(DAY, @target)
END
- CASE
WHEN @source = DATEADD(MONTH, DATEDIFF(MONTH, -1, @source), -1) THEN 30
ELSE DATEPART(DAY, @source)
END
END + 30 * DATEDIFF(MONTH, @source, @target)
END

Print | posted on Sunday, March 15, 2009 4:12 PM | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

## #re: Excel DAYS360 clone

Thanks for this awesome function! Saved me tons of hours.
1/19/2010 6:51 AM | W

## #re: Excel DAYS360 clone

Almost a clone, I had to make one slight weak for February to work correctly. Thanks for posting this.

SELECT [dbo].[Days360]('2/28/2009','8/31/2009',0)
MS SQL Server 2008 R2 Returns 181

=DAYS360("2/28/2009","8/31/2009",FALSE)
Excel 2010 Returns 180

DATEPART(DAYOFYEAR, @source) = 60
Should be
DATEPART(DAYOFYEAR, @source) IN (59,60)

And if you want to get technical... Excel recognizes 2/29/1900 as a calendar day which SQL Server does not nor does VBA.
9/24/2012 8:41 PM | Visnovec
Comments have been closed on this topic.