Fiscal Year Date Query

I need to run a query against a date field in Oracle 9i which determines whether the date within the current Fiscal Year.  (Oct-Sept).

How would I do that?  What's the eaisiest, portable way?
lcorAsked:
Who is Participating?
Advertise Here
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
date functions are not portable as of today. every database has it's own function names.

so, to be portable, you have only 1 solution:
 create a table with 3 (4) columns

* start_date
* end_date
* fiscal_year_display
* key_field

I guess the rest should be obvious, apart from the decision how to fill this table (I use 100 years and supply a button in the application that allows to add/edit rows for the future
0
 
sathyagiriConnect With a Mentor Commented:
0
 
Arthur_WoodConnect With a Mentor Commented:
here is a 'trick' which will return the correct fiscal year, without the need to add a new table to do the conversion.

what you want to do is to ADD 3 months to the actual date, and then take the Year of the result (the three months will effectively advancce OCTOBER of the current year to JANUARY of the FOLLOWING year, and the YEAR function will then return the 'correct Fiscal Year', as the Year to which the follong January belongs).  You do this only to get the Fiscal Year of the data, not to actually modify the dates in any way.

Select <Date_Field>,TO_CHAR(Add_Months(<DATE_Field>, 3), 'YYYY') as Fiscal_Year from YourTable

Change <Date_Field> to the name of the Date field in your table.

AW
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
AW: your trick is not portable :-)
to_char, add_months do not exist in SQL Server and MySQL (to name only these)...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.