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
Legacy Comments
W
2010-01-19 |
re: Excel DAYS360 clone Thanks for this awesome function! Saved me tons of hours. |
Visnovec
2012-09-24 |
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. |