Date falls within Fiscal Year Query

i have a date field  (DATE datatype in Oracle 9i) in a table along with other fields. I need to have an sql query which filters out rows that are only in the current fiscal year.

The sql query reads like this:

select other_field from table where date_field falls within current fiscal year
lcorAsked:
Who is Participating?
 
MikeOM_DBACommented:

Try:

select * from the_table
where to_char(add_months(trunc(the_date),+3),'YYYY') = to_char(trunc(sysdate),'yyyy')

Gives current FY.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
No problem.  Please define 'fiscal year'
0
 
lcorAuthor Commented:
The solution doesn't have to be portable so it can be Oracle specific
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
lcorAuthor Commented:
fiscal year is Oct - Sept
0
 
Patrick MatthewsCommented:
Jim,

Ton of FY questions this week.  Must be lots of companies with July-June FY closing the books :)

Regards,

Patrick
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Yep...

The Quick & Dirty way would be to embed the dates in your query...

select other_field from table where date_field BETWEEN '10/1/2005' AND '9/30/2006'

The better way (which most developers use) is to have a table of days (tbl_days, tbl_time, or tbl_months for just month if that floats your boat) where you have a column that determines fiscal year.  Then you can use queries that go like this...

select other_field from table
INNER JOIN tbl_time ON table.datefield = time.datefield
where tbl_time.FY = '2005'
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
re: tbl_time, in mine I also have columns for fiscal month, fiscal week, fiscal quarter, calendar month, calendar week, holiday (yes/no), which can be real darn handy.
0
 
sathyagiriCommented:
0
 
MikeOM_DBACommented:

PS: If sysdate within FY, otherwise, hard-code the FY:

select * from the_table
where to_char(add_months(trunc(the_date),+3),'YYYY') = '2006'

0
 
davetheravesmithCommented:
Chaps,

My fiscal year is April to March, so I just use logic like this...

if month(date) < 4 then year(date) -1 else year (date)

What this does is that if the month of the date fall in Jan Feb or Mar, it's Fyear is one less than the real year, otherwise it is the same.

Very simple logic / very simply fomula

Regards

DAVE S
0
 
MikeOM_DBACommented:

Depends on the particular definition of fiscal year, for example:

If fiscal year is Oct'05 - Sept'06, then you have to ADD 3 months to the date to get the actual fiscal year (which would be: 2006).

Example:

to_char(add_months(trunc(the_date),+3),'YYYY')





 
0
 
davetheravesmithCommented:
In my case the fiscal year takes the year of the start month, hence Jan Feb & Mar this year (2006) belong to fiscal year 2005.

Just a question, why all the to-char business?  Wouldn't it be sufficient to just display the numeric returned by the YEAR function?

Regards

DAVE S
0
 
MikeOM_DBACommented:

Then you do need to subtract 9 from the date:

to_char(add_months(trunc(the_date),-9),'YYYY')

the to_char is to convert the DATE to CHAR depending on th eformat supplied YYYY.
0
 
MikeOM_DBACommented:

Are you sure of the fiscal year designation?

Normally fiscal years beginning in the first semester are named after the year of beginning month and fy beginning in second semester are normally named after the ending month's year.

But who know's? Every other company may follow it's own rules.
0
 
lcorAuthor Commented:
MikeOM_DBA's is the best solution..works everytime...thanks
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.