CODEWITHSUNDEEP
sqloracle-databasedate-arithmetic
abs786123
abs786123

Reputation: 609

Current Financial Year to sysdate

I can't seem to find a straightforward sql without delving into PL SQL for always bringing current financial year in which case 01-04-2015 to sysdate. I want this to always update automatically so when it comes next financial year in 01/04/2016 it will bring whatever is held from that date to whenever the report is being run.

If anyone can please shed some light for me. thanks

sql is:

SELECT
  PROPERTY.PRO_MANAGINGCOMPANY_DESCR,
  PROPERTY.PRO_SCHEME_DESCR,
  PROPERTY.PRO_SCHEME,
  SUM(REPAIR_CURRENT.REP_ESTIMATED_COST) as "Estimated Cost",
  nvl(SUM(REPAIR_CURRENT.REP_INVOICED_COST),SUM(REPAIR_CURRENT.REP_ESTIMATED_COST)) as "Estimated Cost Invoiced",
  SUM(REPAIR_CURRENT.REP_INVOICED_COST) as "Invoice Cost",
  to_char(REPAIR_CURRENT.REP_RAISED_DATE,'Mon') as "Month",
  to_number(to_char(to_date(REPAIR_CURRENT.REP_RAISED_DATE,'dd-mon-yy'),'mm')) as "Month No."
FROM
 PROPERTY,
  REPAIR_CURRENT,
  SERVICE_REQUEST
WHERE
  ( SERVICE_REQUEST.SRQ_PRO_REFNO=PROPERTY.PRO_REFNO  )
  AND  ( REPAIR_CURRENT.REP_SRQ_NO=SERVICE_REQUEST.SRQ_NO  )
  AND  
  (
  --PROPERTY.PRO_SCHEME  =   ( '00054'  )
   --AND
   REPAIR_CURRENT.REP_RAISED_DATE  BETWEEN  '01-APR-2015'  AND  sysdate
   AND
   REPAIR_CURRENT.REP_STATUS  <>  'CAN'
  )
GROUP BY
  PROPERTY.PRO_MANAGINGCOMPANY_DESCR,
  PROPERTY.PRO_SCHEME_DESCR,
  PROPERTY.PRO_SCHEME,
  to_char(REPAIR_CURRENT.REP_RAISED_DATE,'Mon'),
  to_number(to_char(to_date(REPAIR_CURRENT.REP_RAISED_DATE,'dd-mon-yy'),'mm'))

Upvotes: 1

Views: 5122

Answers (6)

Mayur
Mayur

Reputation: 1

Following SQLreturns start and end date for Financial Year of current date.

SELECT 
TO_DATE('01-04' ||   EXTRACT(YEAR FROM add_months(sysdate, -3)),'DD-MM-RRRR') from_dt , 
TO_DATE('31-03' ||   EXTRACT(YEAR FROM add_months(sysdate, 9)),'DD-MM-RRRR') to_dt from dual;

For any random date, you can use the following SQL: example for 01-04-2020

SELECT 
TO_DATE('01-04' ||   EXTRACT(YEAR FROM add_months(to_date('01-04-2020','DD-MM-RRRR'), -3)),'DD-MM-RRRR') from_dt , 
TO_DATE('31-03' ||   EXTRACT(YEAR FROM add_months(to_date('01-04-2020','DD-MM-RRRR'), 9)),'DD-MM-RRRR') to_dt from dual;

Upvotes: 0

Lalit Kumar B
Lalit Kumar B

Reputation: 49102

REPAIR_CURRENT.REP_RAISED_DATE BETWEEN '01-APR-2015' AND sysdate

Firstly, '01-APR-2015' is not a DATE it is a string. You must always use TO_DATE along with proper format model to explicitly convert the string into DATE. Or, use the ANSI Date literal as you are not concerned with the time portion. It uses a fixed format 'YYYY-MM-DD'.

Now, coming to your date arithmetic, you could use a CASE expression to evaluate the financial date depending on the year.

REP_RAISED_DATE 
BETWEEN
   CASE
   WHEN 
      SYSDATE <  ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), 3) 
   THEN
      ADD_MONTHS(TRUNC(SYSDATE, 'YEAR') , -9)
   ELSE 
      ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), 3)
   END 
AND SYSDATE

Basically, SYSDATE >= ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), 3) is to check whether SYSDATE is greater than 1-APR of current year. And, SYSDATE < ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), 15) is to check whether it is between JAN and MARCH of next year.

For example,

SQL> SELECT
  2     CASE
  3       WHEN
  4          SYSDATE <  ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), 3)
  5       THEN
  6          ADD_MONTHS(TRUNC(SYSDATE, 'YEAR') ,-9)
  7       ELSE
  8          ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), 3)
  9     END FINANCIAL_YEAR
 10  FROM dual;

FINANCIAL
---------
01-APR-15

For date between JAN and MAR of next year:

SQL> SELECT
  2     CASE
  3       WHEN
  4          DATE '2016-02-01' <  ADD_MONTHS(TRUNC(DATE '2016-02-01', 'YEAR'), 3)
  5       THEN
  6          ADD_MONTHS(TRUNC(DATE '2016-02-01', 'YEAR') ,-9)
  7       ELSE
  8          ADD_MONTHS(TRUNC(DATE '2016-02-01', 'YEAR'), 3)
  9     END FINANCIAL_YEAR
 10  FROM dual;

FINANCIAL
---------
01-APR-15

Upvotes: 0

David Aldridge
David Aldridge

Reputation: 52376

This works for any date:

REPAIR_CURRENT.REP_RAISED_DATE
  BETWEEN Add_Months(Trunc(Add_Months(sysdate,-3),'YYYY'),3)
      AND Sysdate

Basically, subtract three months, truncate to the year, and add three months back on.

To just get the financial year for a date, use:

Extract(Year from Add_Months(Trunc(Add_Months(sysdate,-3),'YYYY'),3))

Upvotes: 1

abs786123
abs786123

Reputation: 609

Thank you, the following worked! add_months(trunc(sysdate,'year'),3) AND sysdate

thank you all for your input :)

Upvotes: 0

MT0
MT0

Reputation: 168440

SELECT *
FROM   your_table
WHERE  datetime >= CASE
                   WHEN SYSDATE < TRUNC( SYSDATE, 'YEAR' ) + INTERVAL '3' MONTH 
                   THEN TRUNC( SYSDATE, 'YEAR' ) - INTERVAL '9' MONTH
                   ELSE TRUNC( SYSDATE, 'YEAR' ) + INTERVAL '3' MONTH
                   END;

Upvotes: 0

pablomatico
pablomatico

Reputation: 2242

If you just want to get the beginning of the fiscal year for the current date:

SELECT TO_DATE('01-04' || CASE 
                            WHEN EXTRACT(MONTH FROM SYSDATE) > 4 THEN 
                              EXTRACT(YEAR FROM SYSDATE) 
                            ELSE 
                              EXTRACT(YEAR FROM SYSDATE)-1 
                            END, 'DD-MM-RRRR') FISCAL_YEAR 
FROM DUAL

Upvotes: 1

Related Questions