Finding the first day of the current Fiscal Year 1

[TomCarnahan]

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?


--- Tom

 

[Ascalonian]

Was your answer on p2p incorrect?

 

[TomCarnahan]

No ... it was a good answer.

--- Tom

 

[ianoctdec]

Tnis would be one way to get it:

SELECT CASE WHEN TO_CHAR(SYSDATE, 'MM') IN ('10', '11', '12') THEN '01-OCT-' || TO_CHAR(SYSDATE, 'YYYY') ELSE '01-Oct-' || TO_CHAR(TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY') ) - 1 ) END
FROM
DUAL

Dana

 

[TomCarnahan]

MickeyJudd -- the post on P2P was almost correct.

The person who answered was an old SS developer and was using the "+" sign (a SServer convention) vs. the Oracle "||" (double pipe symbols) to concatenate text strings. The correct answer is now there.

Thanks!

--- Tom