Tuesday, December 21, 2010

How to find the financial year starting date based on tansaction date

Example : Financial period April to March


select '01-APR-'|| decode (to_char (to_date (:transaction_date), 'MM'), '01', to_char (to_number (to_char (to_date (:transaction_date), 'YYYY')) - 1), '02', to_char (to_number (to_char (to_date (:transaction_date), 'YYYY')) - 1), '03', to_char (to_number (to_char (to_date (:transaction_date), 'YYYY')) - 1), to_char (to_number (to_char (to_date (:transaction_date), 'YYYY'))))yearfrom dual

Transaction date : 31-DEC-2009
Result : 01-APR-2009


Transaction date : 01-MAR-2010
Result : 01-APR-2009

No comments: