How to get Quarter from a date in TSQL

问题:

I have a different dates in a column .For example 20080102,20070821

I want to convert this date in Year and quarter .

Below is the output: 
Year       Quarter
2008      2008-Q1
2007      2007-Q3

 

select left(date,4) as year from table 

How can i get the Quarter level details .

回答1:

SELECT DATEPART(QUARTER, @date)

This returns the quarter of the @date, assuming @date is a DATETIME.

回答2:

SELECT DATENAME(Quarter, CAST(CONVERT(VARCHAR(8), datecolumn) AS DATETIME))

回答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.

回答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;

回答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.

回答6:

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

回答7:

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

回答8:

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.

回答9:

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.

回答10:

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

回答11:

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.

回答12:

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