Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Finding the first day of the current Fiscal Year 1

Status
Not open for further replies.

TomCarnahan

Programmer
Dec 7, 2002
123
US
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
 
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
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top