Using SQL to Convert Date to Financial Period Key

Taking a Date (ie 1st July 2020) and converting it to Financial Period Key (ie 202001). Built for Australia style Financial Year

me@jaykilleen.com wrote this about 1 year ago and it was last updated about 1 year ago.

sql

← Back to the Posts

This is using an Australian standard Financial Year (ie July to June).

Given a date in a column of your table. You want to convert that date into a Financial Period Key.

This is working for me where you have a table called Financial Period which is a simple

Financial Period

id financial_year financial_month calendar_year calendar_month month_name short_month_name financial_quarter calendar_quarter financial_quarter_name calendar_quarter_name
201801 2018 1 2017 7 July Jul 1 3 Q1 Q3
201802 2018 2 2017 8 August Aug 1 3 Q1 Q3
201803 2018 3 2017 9 September Sep 1 3 Q1 Q3
201804 2018 4 2017 10 October Oct 2 4 Q2 Q4

Now given a date like 1/07/2020 (1st July 2020) you want to convert it to the Financial Period Key of Financial Period Id like 202001.
In my example below, I also already have a column with the Financial Year.
I'll update this at some stage so I am working purely from the date.

CONCAT(_.financial_year,LPAD(IF(MONTH(_.invoice_date)>6,MONTH(_.invoice_date)-6,MONTH(_.invoice_date)+6),2,0)) AS financial_period_id,

Sometimes, if you date is stored as string in then you will need to convert your string to a date first.
For that, you can use the MySQL STR_TO_DATE method like below:

MONTH(STR_TO_DATE(_.invoice_date,'%d-%b-%Y'))