How to Get Calendar Quarter from a Date in Tsql

Calculate quarter for dates given an example end date and quarter number

Assuming that you have two input variables:

declare @quarter_end date = '2021-01-31';
declare @current_quarter int = 4;

You can calculate the first month of financial year:

declare @first_month_of_fy int = (month(@quarter_end) - @current_quarter * 3 + 12) % 12 + 1;
-- 2 i.e. February

And use that value to calculate the quarter and year for any date using some math:

select *
from (values
    ('2020-12-15'),
    ('2021-01-15'),
    ('2021-12-15'),
    ('2022-01-15')
) as t(testdate)
cross apply (select
    (month(testdate) - @first_month_of_fy + 12) % 12 + 1
) as ca1(month_of_fy)
cross apply (select
    (month_of_fy - 1) / 3 + 1,
    year(dateadd(month, 12 - month_of_fy, dateadd(day, - day(testdate) + 1, testdate)))
) as ca2(fy_quarter, fy_year)

DB<>Fiddle

Get dates in 4 quarters in SQL Server

Not sure if this is what you want to achieve. But I'd suggest using MONTH and CASE statement. This will properly label data which quarter they belong to.

SELECT 
    CASE
        WHEN MONTH(FieldDate) >= 1 AND MONTH(FieldDate) <=3
        THEN
            'Q1'
        WHEN MONTH(FieldDate) >= 4 AND MONTH(FieldDate) <=6
        THEN
            'Q2'
        WHEN MONTH(FieldDate) >= 7 AND MONTH(FieldDate) <=9
        THEN
            'Q3'
        WHEN MONTH(FieldDate) >= 10 AND MONTH(FieldDate) <=12
        THEN
            'Q4'
    END 'QUARTER',
    Field1,
    Field2,
    ...
    FieldX 
FROM 
    [DataBase].[dbo].[SomeTable];
WHERE
    YEAR(FieldDate) = @YEAR

Find first and last dates of quarter from system date SQL query including quarter

Perhaps this will help. However, Aaron is 100% correct in suggesting a calendar table.

Select *
      ,[Quarter] = datename(year,StartDate)+'Q'+datename(quarter,StartDate)
 From  (values (dateadd(QUARTER, datediff(QUARTER, 0, getdate()) - 1, 0)
               ,dateadd(DAY, -1, dateadd(QUARTER, datediff(QUARTER, 0, getdate()), 0)))
       )A(StartDate,EndDate)

Retults

StartDate   EndDate     Quarter
2021-10-01  2021-12-31  2021Q4

How to create customized quarter based on a given date column sql sql server

you need to create lookup table

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=816974201a48ed0bdf6782c121e1f8db

then you will use this table and join it to using between ( start_q end_q ) to get your customized quarter.

How to find the last day of a Quarter, Half Year and Year of current Year in SQL Server?

Since you tag datediff, this is a solution using dateadd / datediff

; with dates as
(
    select  [date] = convert(date, '2020-01-01')
    union all
    select  [date] = dateadd(month, 1, [date])
    from    dates
    where   [date]  < '2020-12-01'
)
select  [date],
        dateadd(quarter, datediff(quarter, 0, [date]) + 1, -1) as [Last Day of Quarter],
        dateadd(quarter, datediff(quarter, 0, [date]) / 2 * 2 + 2, -1) as [Last Day of Half Year],
        dateadd(year, datediff(year, 0, [date]) + 1, -1) as [Last Day of Year]
from    dates

db<>fiddle

Datetime function: semester, quarter

DATEPART is suitable for your case.

CASE WHEN DATEPART(quarter,[Date]) >= 3 THEN 2 ELSE 1 END as [Semester],
DATEPART(quarter,[Date]) as [Quarter]

How to get the quarter corresponding to a date in Oracle SQL 19c

I will use sysdate for illustration - you can replace that with any expression of date data type.

The query returns a string (of a single character, a digit between 1 and 4); if you need a number value, wrap it within to_number().

select to_char(sysdate, 'Q') as qtr from  dual;

QTR
---
3

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"

---

Related Topics