Peter Larsson Blog

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

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.