CODEWITHSUNDEEP
phpmysql
SK2017
SK2017

Reputation: 773

Convert Date value to Accounting/Tax Year in mySQL

Is there anyway to convert a Date format value to a taxYear/Accounting year format, so that they could be then grouped by?

EG With Accounting/Tax year from April 01 - March 31 :

Date_Col         Tax_Year/AccountingYear
2016-01-01   === 15/16 
2015-05-25   === 15/16
2015-03-05   === 14/15

Is it possible within a mysql query, or a php function that can do the same job?

Upvotes: 1

Views: 292

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

You can do the conversion by subtracting 3 months/adding 9 months and using something like:

select concat(date_format(date_sub(date_col, interval 3 month), '%y'),
              '/',
              date_format(date_add(date_col, interval 9 month), '%y')
             ) as tax_year

EDIT:

You can try this:

select concat(year(date_sub(date_col, interval 3 month),
              '/',
              year(date_add(date_col, interval 9 month)
             ) as tax_year

This version should return "2016/2017". I prefer 4-digit years in any case.

Upvotes: 1

Related Questions