CODEWITHSUNDEEP
sqlstringsqlitedatetimesql-date-functions
user14140366
user14140366

Reputation: 29

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.

Upvotes: 1

Views: 3460

Answers (3)

forpas
forpas

Reputation: 164174

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270723

I would do it with arithmetic rather than a case expression:

select floor( (strftime('%m', close_date) + 2) / 3 ) as quarter

Upvotes: 1

GMB
GMB

Reputation: 222632

You can extract the month part and use a case expression:

select
    close_date,
    case 
        when 0 + strftime('%m', close_date) between  1 and  3 then 'Q4'
        when 0 + strftime('%m', close_date) between  4 and  6 then 'Q1'
        when 0 + strftime('%m', close_date) between  7 and  9 then 'Q2'
        when 0 + strftime('%m', close_date) between 10 and 12 then 'Q3'
    end as quarter
from mytable

The addition of 0 is there to force the conversion of the result of strftime() to a number.

This could also be expressed using date artihmetics (which lets you generate the fiscal year too):

select 
    close_date,
    strftime('%Y', close_date, '-3 months') 
        || 'Q' || ((strftime('%m', close_date, '-3 months') - 1) / 4) as year_quarter
from mytable

Upvotes: 1

Related Questions