Taking a Date (ie 1st July 2020) and converting it to Financial Period Key (ie 202001). Built for Australia style Financial Year
email@example.com wrote this over 2 years ago and it was last updated over 2 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
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
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: