Home Forums SQL Server 2008 T-SQL (SS2K8) Convert the stored procedure to "Standard SQL Select" RE: Convert the stored procedure to "Standard SQL Select"

  • Lynn Pettis (4/12/2014)


    There is another way, look at the last Fiscal Year calculation:

    WITH SAMPLE_DATE(XDATE) AS

    (

    SELECT CONVERT(DATETIME2(0),XDATE,120) AS XDATE

    FROM (VALUES

    ('2007-11-22 00:00:00.000'),('2007-10-03 00:00:00.000')

    ,('2007-09-21 00:00:00.000'),('2006-11-07 00:00:00.000')

    ,('2008-04-29 00:00:00.000'),('2006-10-13 00:00:00.000')

    ,('2008-05-07 00:00:00.000'),('2008-04-05 00:00:00.000')

    ,('2007-08-05 00:00:00.000'),('2008-06-02 00:00:00.000')

    ,('2007-12-26 00:00:00.000'),('2007-09-26 00:00:00.000')

    ,('2008-01-31 00:00:00.000') ) AS X(XDATE)

    )

    SELECT

    SD.XDATE

    ,YEAR(SD.XDATE) + SIGN(1 + SIGN(MONTH(SD.XDATE) - 11 )) AS FiscalYear

    ,year(dateadd(month,2,SD.XDATE)) As AlsoFiscalYear -- This looks so much cleaner IMHO

    FROM SAMPLE_DATE SD;

    Nice 😎

    And around 10% faster than my code :pinch: