How to get Quarter from a date in Firebird SQL

Question

I can easily get total sales in this month and previous month.

SELECT ‘This Mount’,  SUM(Price) FROM Sales 
WHERE EXTRACT(MONTH FROM OrderDate) = EXTRACT(MONTH FROM CURRENT_DATE)   
  AND EXTRACT(YEAR FROM OrderDate) = EXTRACT(YEAR FROM CURRENT_DATE)   
Union All
SELECT ‘Previous Month’,  SUM(Price) FROM Sales 
WHERE EXTRACT(MONTH FROM OrderDate) = EXTRACT(MONTH FROM CURRENT_DATE)   
  AND EXTRACT(YEAR FROM OrderDate) = EXTRACT(YEAR FROM CURRENT_DATE)

I want to get the total sales in this quarter and previous quarter.

Getting quarter from a date is very easy with MS-SQL as follows:

SELECT DATEPART(QUARTER, @date)

How can I do this with Firebird?

Andrej Kirejeŭ · Accepted Answer · 2015-11-16T07:22:20.823

6

Use DECODE function in conjunction with EXTRACT:

SELECT
  DECODE(EXTRACT(MONTH FROM <date_field>),
    1, 'I',
    2, 'I',
    3, 'I',
    4, 'II',
    5, 'II',  
    6, 'II',  
    7, 'III',  
    8, 'III',  
    9, 'III',
    'IV')  
  FROM
    <some_table>

Or just

  SELECT
    (EXTRACT(MONTH FROM <date_field>) - 1) / 3 + 1
  FROM 
    <some_table>

edited Nov 16 '15 at 07:22

answered Nov 16 '15 at 04:44

Andrej Kirejeŭ

5,381
2
26
31

1

`EXTRACT(MONTH FROM ) / 3 + 1` This doesn't work. Because (December) 12/3 + 1 = 5 – Zebedee Nov 16 '15 at 06:42
1

Fixed. Should extract 1 from a month number. – Andrej Kirejeŭ Nov 16 '15 at 07:23

score 1 · Answer 2 · edited May 02 '17 at 23:48

1

SELECT dates,
   EXTRACT(MONTH from dates) as SalesMonth, 
   floor(((EXTRACT(MONTH from dates)-1) / 3.0 + 1))  as QTR 
from CustomerPO 
where ((dates > '1/1/2016') and (dates < '12/31/2016')) 
order by dates

Here, 'dates' is the field name of Order table 'CustomerPO'

edited May 02 '17 at 23:48

answered May 02 '17 at 23:22

Henry Kim

11
2

score 0 · Answer 3 · answered Feb 05 '21 at 15:44

0

SELECT dates,
   EXTRACT(MONTH from dates)            as SalesMonth, 
   ceil(EXTRACT(MONTH from dates) / 3)  as QTR 
from CustomerPO 
where ((dates > '1/1/2016') and (dates < '12/31/2016')) 
order by dates

answered Feb 05 '21 at 15:44

TJC

1

2

Please don't post only code as answer, but also provide an explanation what your code does and how it solves the problem of the question. Answers with an explanation are usually more helpful and of better quality, and are more likely to attract upvotes – Pouria Hemi Feb 05 '21 at 17:24

How to get Quarter from a date in Firebird SQL

3 Answers3