?
Solved

Fiscal Year Date Query

Posted on 2006-07-10
4
Medium Priority
?
1,973 Views
Last Modified: 2011-10-03
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?
0
Comment
Question by:lcor
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 800 total points
ID: 17075092
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
 
LVL 14

Assisted Solution

by:sathyagiri
sathyagiri earned 600 total points
ID: 17075127
0
 
LVL 44

Assisted Solution

by:Arthur_Wood
Arthur_Wood earned 600 total points
ID: 17075528
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17075586
AW: your trick is not portable :-)
to_char, add_months do not exist in SQL Server and MySQL (to name only these)...
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question