datefirebirddatepart

How to get Quarter from a date in Firebird SQL

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?

Solution

  • 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>