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: