How to get calendar Quarter from a date in TSQL
SqlSql ServerTsqlSql Server-2005Sql Problem Overview
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?
Sql Solutions
Solution 1 - Sql
SELECT DATEPART(QUARTER, @date)
This returns the quarter of the @date, assuming @date is a DATETIME.
Solution 2 - Sql
SELECT DATENAME(Quarter, CAST(CONVERT(VARCHAR(8), datecolumn) AS DATETIME))
Solution 3 - Sql
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 - Sql
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
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 - Sql
I did it like this (I am using SQL Server):
SELECT 'Q' + cast(DATEPART(QUARTER, GETDATE()) as varchar(1)) + ' - ' + cast(DATEPART(YEAR, GETDATE()) as varchar(4)) AS 'Date Quarter'
Solution 6 - Sql
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.
Solution 7 - Sql
To get the exact output you requested, you can use the below:
CAST(DATEPART(YEAR, @Date) AS NVARCHAR(10)) + ' - Q' + CAST(DATEPART(QUARTER, @Date) AS NVARCHAR(10))
This will give you an outputs like: "2015 - Q1", "2013 - Q3", etc.
Solution 8 - Sql
nice excuse to muck around with CONVERT. Probably prettier ways of doing it:
create table the_table
(
[DateKey] INT,
)
insert into the_table
values
(20120101),
(20120102),
(20120201),
(20130601)
WITH myDateCTE(DateKey, Date) as
(
SELECT
DateKey
,[Date] = CONVERT(DATETIME, CONVERT(CHAR(8),DateKey),112)
FROM the_table
)
SELECT
t.[DateKey]
, m.[Date]
, [QuarterNumber] = CONVERT(VARCHAR(20),Datepart(qq,Date))
, [QuarterString] = 'Q' + CONVERT(VARCHAR(20),Datepart(qq,Date))
, [Year] = Datepart(yyyy,Date)
, [Q-Yr] = CONVERT(VARCHAR(2),'Q' + CONVERT(VARCHAR(20),Datepart(qq,Date))) + '-' + CONVERT(VARCHAR(4),Datepart(yyyy,Date))
FROM
the_table t
inner join myDateCTE m
on
t.DateKey = m.DateKey
Solution 9 - Sql
SELECT
Q.DateInQuarter,
D.[Year],
Quarter = D.Year + '-Q'
+ Convert(varchar(1), ((Q.DateInQuarter % 10000 - 100) / 300 + 1))
FROM
dbo.QuarterDates Q
CROSS APPLY (
VALUES (Convert(varchar(4), Q.DateInQuarter / 10000))
) D ([Year])
;
See a Live Demo at SQL Fiddle
Solution 10 - Sql
Assuming field data type INT and field name "mydate". In the OP question, the INT date values when converted to string are ISO date literals.
select DatePart(QUARTER, cast(cast(mydate as char(8)) as date))
You can use datetime if using older server version.
Solution 11 - Sql
Try this one
SELECT CONCAT (TO_CHAR(sysdate,'YYYY-'),concat ('Q',TO_CHAR(sysdate,'Q') ))from dual
Replace sysdate with your own column name with date type format and dual with your table name
Solution 12 - Sql
You have to convert the integer to a char(8) then a datetime. then wrap that in SELECT DATEPART(QUARTER, [date])
You will then have to convert the above to character and add on the '-' + year (also converted to char)
The arithmetic overflow above is caused by omitting the initial convert to a character type.
I would be inclined to abstract the conversion to date-time using views where possible and then use the quarter function and character conversion as and when required.
Solution 13 - Sql
Here you see one of the more alternatives :
SELECT CASE
WHEN @TODAY BETWEEN @FY_START AND DATEADD(M, 3, @FY_START) THEN 'Q1'
WHEN @TODAY BETWEEN DATEADD(M, 3, @FY_START) AND DATEADD(M, 6, @FY_START) THEN 'Q2'
WHEN @TODAY BETWEEN DATEADD(M, 6, @FY_START) AND DATEADD(M, 9, @FY_START) THEN 'Q3'
WHEN @TODAY BETWEEN DATEADD(M, 9, @FY_START) AND DATEADD(M, 12, @FY_START) THEN 'Q4'
END
Solution 14 - Sql
Using the function MONTH which returns the month as a number, we can easily calculate the quarter.
select date, CEILING((MONTH(date) * 4) / 12) quarter from dual
Solution 15 - Sql
declare @TempTable table([Date] datetime)
insert into @TempTable([Date])
values('2008-01-02'),('2007-08-21')
select datepart(year, [Date]) as [year]
,convert(varchar(10),datepart(year, [Date])) + '-Q' + convert(varchar(3),datename(quarter, [Date])) as quarter_with_year
,datefromparts(datepart(year, [Date]),(convert(varchar(3),datename(quarter, [Date])) * 3)-2,1) as quarter_startdate
,eomonth(datefromparts(datepart(year, [Date]),convert(varchar(3),datename(quarter, [Date])) * 3,1)) as quarter_enddate
FROM @TempTable
> This returns the year,quarter, and start end date of quarter.
Solution 16 - Sql
select
your_date,
CONCAT (TO_CHAR(your_date, 'YYYY-'),concat ('Q', date_part(quarter, your_date) ))
from table;
Solution 17 - Sql
Try the following:
CONCAT(datepart(yyyy,DATE),'-', DATEPART(qq,DATE))
It returns:
yyyy-q
Example: 2017-3 for 2017-07-11