Showing what quarter of a financial year a date is in
This should work:-
SELECT
MyDate,
CASE
WHEN MONTH(MyDate) BETWEEN 1 AND 3 THEN convert(char(4), YEAR(MyDate) - 1) + 'Q3'
WHEN MONTH(MyDate) BETWEEN 4 AND 6 THEN convert(char(4), YEAR(MyDate) - 1) + 'Q4'
WHEN MONTH(MyDate) BETWEEN 7 AND 9 THEN convert(char(4), YEAR(MyDate) - 0) + 'Q1'
WHEN MONTH(MyDate) BETWEEN 10 AND 12 THEN convert(char(4), YEAR(MyDate) - 0) + 'Q2'
END AS Quarter
FROM
MyTable
Output:-
MyDate Quarter
---------- --------
2011-01-01 "2010Q3"
2011-04-01 "2010Q4"
2011-07-01 "2011Q1"
2011-10-01 "2011Q2"
I think it would be easiest to do this with CASE
SELECT
date
, CASE
WHEN MONTH(date) BETWEEN 7 AND 9 THEN 'First Quarter'
WHEN MONTH(date) BETWEEN 10 AND 12 THEN 'Second Quarter'
WHEN MONTH(date) BETWEEN 1 AND 3 THEN 'Third Quarter'
WHEN MONTH(date) BETWEEN 4 AND 6 THEN 'Fourth Quarter'
END AS quarter
FROM Stuff
This method doesn't require a CASE statement, and works for any monthly fiscal calendar.
DECLARE @firstMonthOfFiscalQ1 int = 7; --1=January
SELECT DateColumn
, FLOOR(((12 + MONTH(DateColumn) - @firstMonthOfFiscalQ1) % 12) / 3 ) + 1 AS FiscalQuarter
FROM SomeTable
WHERE 1=1;