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 over 3 years ago and it was last updated over 3 years ago.
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'))