How to get calendar Quarter from a date in TSQL

Question

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

User · Answer

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

User · Answer

SELECT DATEPART QUARTER   date    This returns the quarter of the  date  assuming  date is a DATETIME

User · Answer

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

User · Answer

nice excuse to muck around with CONVERT  Probably prettier ways of doing it   live test on SQLfiddle here  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

User · Answer

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

User · Answer

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     gt   q MonthBegin AND       DATEPART m  CONVERT DATETIME  Dates  date     lt   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      gt   q MonthBegin AND         DATEPART m  CONVERT DATETIME  CONVERT VARCHAR 8   Dates  date      lt   q MonthEnd

User · Answer

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

User · Answer

SELECT DATENAME Quarter  CAST CONVERT VARCHAR 8   datecolumn  AS DATETIME

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Try the following   CONCAT datepart yyyy DATE   -   DATEPART qq DATE     It returns   yyyy-q   Example  2017-3 for 2017-07-11

[sql] How to get calendar Quarter from a date in TSQL

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to sql-server-2005