Skip to content
Advertisement

How to group dates in Quarters in SQLite

I need to group my dates as Quarters, April to June as Q1, Jul to Sep as Q2, Oct to Dec as Q3 and Jan to March as Q4

enter image description here

I need to add another column besides close_dates showing Quarters. I cannot find any date function i can use. Any ideas on this.

Advertisement

Answer

The format of your dates is not YYYY-MM-DD which is the only valid date format for SQLite.
So if you want to extract the month of a date, any date function that SQLite supports will fail.
You must use the string function SUBSTR() to extract the month and then other functions like NULLIF() and COALESCE() to adjust the quarter to your requirement.
Assuming that the format of your dates is DD/MM/YYYY:

SELECT Close_Date,
       'Q' || COALESCE(NULLIF((SUBSTR(Close_Date, 4, 2) - 1) / 3, 0), 4) AS Quarter
FROM tablename

If the format is MM/DD/YYYY then change SUBSTR(Close_Date, 4, 2) to SUBSTR(Close_Date, 1, 2) or just Close_Date because SQLite will implicitly convert the date to a number which will be the starting digits of the date.

See the demo.
Results:

> Close_Date | Quarter
> :--------- | :------
> 01/04/2019 | Q1     
> 01/05/2019 | Q1     
> 01/10/2019 | Q3     
> 01/09/2019 | Q2     
> 01/06/2019 | Q1     
> 01/09/2019 | Q2     
> 01/04/2019 | Q1     
> 01/07/2019 | Q2 
9 People found this is helpful
Advertisement