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>