How to get calendar Quarter from a date in TSQL
I have different dates in a column. For example:
20080102
20070821
I want to convert these dates in Year and calendar quarter. E.g.,
Year Quarter
2008 2008-Q1
2007 2007-Q3
I can get the first column with:
select left(date,4) as year from table
How can I produce the second column?
Solution 1:
SELECT DATEPART(QUARTER, @date)
This returns the quarter of the @date
, assuming @date
is a DATETIME
.
Solution 2:
SELECT DATENAME(Quarter, CAST(CONVERT(VARCHAR(8), datecolumn) AS DATETIME))
Solution 3:
Here's how I do it. Pretty brief and doesn't rely on temp tables.
CAST(year(TheDate) AS char(4)) + '-Q' +
CAST(CEILING(CAST(month(TheDate) AS decimal(9,2)) / 3) AS char(1))
As an example:
SELECT convert(char(10), getdate(), 101) AS TheDate,
CAST(year(getdate()) AS char(4)) + '-Q' +
CAST(CEILING(CAST(month(getdate()) AS decimal(4,2)) / 3) AS char(1)) AS SelectQuarter
This will return:
TheDate SelectQuarter
---------- -------------
07/10/2013 2013-Q3
Obviously the string itself can be changed to suit your own format. Hope this is helpful.
Solution 4:
Here is another option. Use a CTE to define the months of the quarter and then join to it to determine the quarter:
WITH Quarters AS (
SELECT Q = 'Q1', MonthBegin = 1, MonthEnd = 3 UNION
SELECT Q = 'Q2', MonthBegin = 4, MonthEnd = 6 UNION
SELECT Q = 'Q3', MonthBegin = 7, MonthEnd = 9 UNION
SELECT Q = 'Q4', MonthBegin = 10, MonthEnd = 12
)
SELECT
[Year] = DATEPART(yyyy, CONVERT(DATETIME, Dates.[date])),
[Quarter] = CONVERT(VARCHAR(4), DATEPART(yyyy, CONVERT(DATETIME, Dates.[date]))) + '-' + q.Q
FROM
(VALUES
('20080102'),
('20070821')
) AS Dates ([date])
INNER JOIN Quarters q ON
DATEPART(m, CONVERT(DATETIME, Dates.[date])) >= q.MonthBegin AND
DATEPART(m, CONVERT(DATETIME, Dates.[date])) <= q.MonthEnd;
Returns:
Year Quarter
----- ----------
2008 2008-Q1
2007 2007-Q3
SQL Fiddle
Handle column type of int (04/23/2014):
WITH Quarters AS (
SELECT Q = 'Q1', MonthBegin = 1, MonthEnd = 3 UNION
SELECT Q = 'Q2', MonthBegin = 4, MonthEnd = 6 UNION
SELECT Q = 'Q3', MonthBegin = 7, MonthEnd = 9 UNION
SELECT Q = 'Q4', MonthBegin = 10, MonthEnd = 12
)
SELECT
[Year] = DATEPART(yyyy, CONVERT(DATETIME, CONVERT(VARCHAR(8), Dates.[date]))),
[Quarter] = CONVERT(VARCHAR(4), DATEPART(yyyy, CONVERT(DATETIME, CONVERT(VARCHAR(8), Dates.[date])))) + '-' + q.Q
FROM
(VALUES
(20080102),
(20070821)
) AS Dates ([date])
INNER JOIN Quarters q ON
DATEPART(m, CONVERT(DATETIME, CONVERT(VARCHAR(8), Dates.[date]))) >= q.MonthBegin AND
DATEPART(m, CONVERT(DATETIME, CONVERT(VARCHAR(8), Dates.[date]))) <= q.MonthEnd;
Solution 5:
Since your date field data is in int
you will need to convert it to a datetime:
declare @date int
set @date = 20080102
SELECT Datename(quarter, Cast(left(@date, 4) + '-'
+ substring(cast(@date as char(8)), 5, 2) + '-'
+ substring(cast(@date as char(8)), 7, 2) as datetime)) as Quarter
or
SELECT Datename(quarter, Cast(left(@date, 4) + '-'
+ substring(cast(@date as char(8)), 5, 2) + '-'
+ right(@date, 2) as datetime)) as quarter
Then if you want the Q1
added:
SELECT left(@date, 4) + '-Q' + Convert(varchar(1), Datename(quarter, Cast(left(@date, 4) + '-'
+ substring(cast(@date as char(8)), 5, 2) + '-'
+ right(@date, 2) as datetime))) as quarter
My advice would be to store your date
data as a datetime
so then you do not need to perform these conversions.