Thinking outside the box

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 ]