SQL: Convert dd/mm/yyyy to yyyyQ1

Question

How to convert date to financial quarters: 3/31/2018 to 2018q1

I pulled a dataset from the FDIC website. Their date format is currently dd/mm/yyyy.

I am interested in creating a Scatter Plot/Bubble Chart using Gapminder.

However Gapminder needs each date to be converted to financial quarters. e.g. yyyyq1, yyyyq2, yyyyq3, or yyyyq4. e.g. 20017q1, 20017q2, 20017q3, or 2017q4.

This query needs to convert the date to financial quarters, but doesn't already do so. What needs to be added to convert "repdte" output dd/mm/yyyy to yyyyq1?

SELECT 

PCR.name,
PCR.repdte as Quarter, 
PCR.idlncorr as NetLoansAndLeasesToCoreDeposits,
CAST(LD.IDdeplam as int) as DepositAccounts$GreaterThan$250k
from All_Reports_20180630_Performance_and_Condition_Ratios as PCR 
join 
'All_Reports_20180630_Deposits_Based_on_the_$250,000_Reporting_Threshold' 
 as LD on PCR.cert = LD.cert
UNION ALL
SELECT
PCR.name, 
PCR.repdte as Quarter,
PCR.idlncorr as NetLoansAndLeasesToCoreDeposits,
CAST(LD.IDdeplam as int) as DepositAccounts$GreaterThan$250k
FROM All_Reports_20180331_Performance_and_Condition_Ratios as PCR
JOIN 
'All_Reports_20180331_Deposits_Based_on_the_$250,000_Reporting_Threshold' 
 as LD on PCR.cert = LD.cert

What I currently have

Quarter   
03/31/2018 

The format that Gapminder needs to render the Bubble Chart:

ReportDate
  2009q1       

Answer 1

I believe that using

substr(PCR.repdte,7,4)||'q'||CAST(1+((substr(PCR.repdte,1,2)-1) / 3) AS INTEGER)

will convert the date for you.

For example, consider the following :-

DROP TABLE IF EXISTS PCR;
CREATE TABLE IF NOT EXISTS PCR (repdte);
INSERT INTO PCR VALUES('01/31/2009'),('02/31/2009'),('03/31/2009'),('04/31/2009'),('05/31/2009'),('06/31/2009'),('07/31/2009'),('08/31/2009'),('09/31/2009'),('10/31/2009'),('11/31/2009'),('12/31/2009');

SELECT PCR.repdte,
    substr(PCR.repdte,7,4)||'q'||CAST(1+((substr(PCR.repdte,1,2)-1) / 3) AS INTEGER) FROM PCR;

Which results in :-

---

Additional

Re comment :-

It works. However, I'm getting an output of '018q2' instead of '2018q2'. What would I change to add a '2' to '018q2'?

This would appear to be due to the date have a variable length day part, that is if the day part is less then 10 then it is a single numeric rather than being padded with 0 and two numerics when 10 or more.

The following could be used :-

replace(substr(PCR.repdte,6),'/','')||'q'||CAST(1+((substr(PCR.repdte,1,2)-1) / 3) AS INTEGER)

• this works by taking the year from from the 6th character and removing the / if it exists, consider the following

:-

DROP TABLE IF EXISTS PCR;
CREATE TABLE IF NOT EXISTS PCR (repdte);
INSERT INTO PCR VALUES('01/31/2009'),('02/1/2009'),('03/31/2009'),('04/31/2009'),('05/1/2009'),('06/31/2009'),('07/31/2009'),('08/1/2009'),('09/31/2009'),('10/31/2009'),('11/31/2009'),('12/31/2009');
SELECT PCR.repdte,
    substr(PCR.repdte,7,4)||'q'||CAST(1+((substr(PCR.repdte,1,2)-1) / 3) AS INTEGER), -- OLD
    replace(substr(PCR.repdte,6),'/','')||'q'||CAST(1+((substr(PCR.repdte,1,2)-1) / 3) AS INTEGER) -- MODIFIED
FROM PCR;

Which results in :-