Pull records based on current fiscal year

You're also able to use ROUND on date types, maybe that will help, look here:

http://www.techonthenet.com/oracle/functions/round_date.php

and you should take a look at
http://www.techonthenet.com/oracle/functions/to_date.php

try something like this (untested)

select
*
from yourtable
where ( datefield >=     (case
                          when to_number(to_char(sysdate,'MM')) >= 10 
                                then add_months(trunc(sysdate,'YYYY'),9)
                                else add_months(trunc(sysdate,'YYYY'),-3)
                         end)
       and datefield <  (case
                          when to_number(to_char(sysdate,'MM')) >= 10 
                                then add_months(trunc(sysdate,'YYYY'),21)
                                else add_months(trunc(sysdate,'YYYY'),9)
                         end)
       )

Select allOpen in new window

{+ edit for got some = in the above, and again a wrong number}

Oracle can do arithmetic with date values, so one possibility is to do something like PortletPaul suggested (but did not explain).  This date arithmetic can be simply adding a number of days to a date (for example: p.PMP_START_DATE + 90) or you can use an Oracle function like "add_months" to add a specific number of months to a date value.

But, in both of those cases you still end up with a date, which in Oracle's way of thinking includes: year, month and day (these can be displayed in any order you like) plus optionally the time of day: hours, minutes and seconds.

It may seem easier to you to work with a "fiscal year" than with Oracle date values and Oracle does offer operators to extract any portion of a "date" (century, year, quarter, month, day, day of week, hour, minute, second, etc.) but keep in mind that when you use operators like this to extract a portion of a date, if you do this in the "where" clause of you query, you prevent Oracle from using any indexes on that date column and you force Oracle to read every record in the table to determine if that record meets your query criteria, or not.  This can make queries painfully slow!

When working with dates in Oracle, I usually find it best to use a "between" comparison in the query, something like this:
where p.PMP_START_DATE between [min_value] and [max_value]
This kind of query allows Oracle to use existing indexes on the p.PMP_START_DATE column.  Depending on the data volumes, and on whether this column is indexed or not, this can make a huge difference in performance compared to something like this syntax:
where extract(PMP_START_DATE,'year') = [year_value]

I realize that I haven't directly answered your question, but I tried to help a bit and indicate some other things you should be aware of when working with dates in Oracle databases.

@markgeer Thank you for all your input! Like I stated, I am brand new to Oracle SQL and I welcome all the input I can get!

Your point of allowing Oracle to utilize the index in the query is a very good point.
I will defiantly use the BETWEEN function for my Date ranges. After all...making the query more efficient is the whole point in putting the WHERE clause in the SQL in the first place!

It helps to know those little nuances between languages. Like Java and their lack of useable date functions.

ok @PortletPaul I finally figured out what you were trying to do.
It took me a minute to catch up but once I figured it out, I just had to make some adjustments and now works perfectly!

Thank you!!

where ( p.pmp_start_date between     (case
                          when to_number(to_char(sysdate,'MM')) > 9
                                then trunc(sysdate,'Q')   --if the current month is October-December then it begins pulling records on October 1st of the same year.
                                else add_months(trunc(sysdate,'YYYY'),-3)  --if the current month is Jan-Sept then it begins pulling records on October 1st of the prior year.
                         end)
  and                                (case
                          when to_number(to_char(sysdate,'MM')) > 9  
                                then add_months(trunc(sysdate,'YYYY'),12) --if the current month is October-December then it stops pulling records on October 1st of the next year.
                                else trunc(sysdate,'Q') --if the current month is Jan-Sept then it stops pulling records on October 1st of the same year.
                         end)
       );

Select allOpen in new window

Can anyone suggest the best way to create this as a FUNCTION?

I can create a FUNCTION that returns only the START DATE or the END DATE but I do not know how I would create a function that would return a date range?
Is that even possible??

Thanks!

Functions by defintion return a single value.  A "value" though could be a text string, and this text string could (possibly) be interpreted as (or converted to) a portion of a SQL statement.  Is this the best way to solve the problem though?

>>When working with dates in Oracle, I usually find it best to use a "between" comparison in the query
I absolutely disagree about the use of between I'm afraid, and urge you to not adopt use of that.

Please see "Beware of Between" for reasons why.

Using between leads to complexities that are completely avoidable. In many cases I'm afraid to say it also leads to error (either by permitting overlaps, or more commonly forcing users to adjust the criteria but in so doing creating gaps).

The most reliable method for filtering dates is through a combination of >= with <
======

Your question asked for an automated way to select date based information for a fiscal year (starting in the 10th month). So for sysdates in months 10,11,12 the year of the FY starting date is unaffected, for all other months the year much be reduced by one.

The same logic occurs for the ending date, if the sysdate is in months 10,11,12 the ending date of the FY is in today's year + 1, otherwise the ending date year is the current year.

Having established year, then the add_months() function is used to either add or deduct months to the relevant year.

I have to admit I was too tired when I answered so I put in what I could and omitted any attempt at explanation. Markgeer did a great job on that - thanks (except we disagree on use of between).

Like markgeer, the direction of the proposed calculations is to ensure you maximize indexes in the query by avoiding calculations on the data. Make the filtering criteria suit that data - don't adjust the data to suit filtering criteria.

& Please don't use between :)

{apologies, an edit was needed for clarity}

by the way, nice job on fixing my code :)

Correct Code and explained throughly.

To PortletPaul:
OK, I read that article:  "Beware of Between".  There was nothing in there that I wasn't aware of.  Yes, the "between" operator in Oracle is inclusive of both the min and max boundary values.  And, yes with "date" columns in particular (because of the optional time component of Oracle "date" values) this can add some complexity.  To me, this is just a matter of preference: either use ">=" plus "<=" or "between".  They will both return the same rows if they are carefully written.

@markgeer
I don't expect to win over everyone, for those who understand the full details of between and the data precision they are analyzing, then of course between can be made useful - but:
many people simply do not understand the inclusive nature of between, or, if they do, they compensate in one of 2 ways
a. by attempting to reduce the high point by some arbitrary time unit (a possible gap), or
b. amend the data precision to suit the criteria (affecting performance)

the alternative route is to stop using between, and these issues fall away.
to me, the choice is very clear

btw: I have witnessed many questions here already where something like this has been used [ between 2012-01-01 and 2012-12-31 ] where it is clear the asker believes somehow that between magically adds "to the end of the day" on the high point.
There is a lot of misunderstanding on this sql feature out there.

@ColB7533
thanks! and thank you for tolerating my rants on between

and @markgeer
thank you for also for tolerating the same rants

ColB7533 - I just kicked out an article that is the SQL Server answer to this question, deals specifically with Fiscal calendar planning --> SQL Server Calendar Table:  Fiscal Years.   Let me know if this helps you, and if yes please click on the 'Good Article' button and provide some feedback.  Thanks.