How to Determine the first day of the current Fiscal Year

tcarnahan · February 13th, 2009, 01:03 AM

I am a SQL Server developer who is trying to to learn Oracle SQL. I am trying to write a query that will 1) determine the month number and if the number is 10, 11, or 12, will return '01-Oct-' of the current Calendar year. If the month number is between 1 and 9, it returns '01- Oct-' of the last Calendar year (YYYY = current Calendar year minus 1 year).

I was playing with the EXTRACT function to get the year and month, but was unable to formulate the '1-Oct-YYYY' where YYYY is the current or previous calendar year.

Could anyone point me to an example of how to do this in Oracle?

Old Pedant · February 13th, 2009, 01:46 AM

http://www.google.com/search?hl=en&c...ns&btnG=Search

which leads to

http://www.psoug.org/reference/date_func.html

So...something like...

Code:

SELECT TO_DATE( CASE WHEN TO_NUMBER( TO_CHAR( SYSDATE,'MM') ) IN ( 10, 11, 12 )
                THEN '1-Oct-' + TO_CHAR(SYSDATE,'YYYY') 
                ELSE '1-Oct-' + TO_CHAR( TO_NUMBER( TO_CHAR(SYSDATE,'YYYY') ) - 1 )
                END ) As FiscalYearStart

And here I thought SQL Server was braindead when it comes to datetime functions! (And it is, and so is Oracle a lot, when compared to Access or MySQL!!!)

tcarnahan · February 16th, 2009, 12:39 PM

This is a good solution except for one small syntactical error ... very easy to make. The code should read:

Code:

SELECT TO_DATE( CASE WHEN TO_NUMBER( TO_CHAR( SYSDATE,'MM') ) IN ( 10, 11, 12 )
                THEN '1-Oct-' || TO_CHAR(SYSDATE,'YYYY') 
                ELSE '1-Oct-' || TO_CHAR( TO_NUMBER( TO_CHAR(SYSDATE,'YYYY') ) - 1 )
                END ) As FiscalYearStart

Because I work so much in SS, I did not catch the concatenation part at first. Oracle uses double pipe symbols vs. "+" for concatenation.

Just the same, I really appreciate your help!

Old Pedant · February 16th, 2009, 09:52 PM

DOH! I KNEW THAT!

I worked with Oracle for a grand total of about 2 months in 2001, but I do remember that! Shame on me! Thanks for the fix!