Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

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 ]

Feedback

Gravatar

# re: Excel DAYS360 clone

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

# 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.

Powered by:
Powered By Subtext Powered By ASP.NET