Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2011
    Posts
    82

    Unanswered: Between Financial Year and End Time Of Last Week

    Hello

    I have written the following syntax to calculate the start time of the financial year and end time of last week

    It was working previously, but when I ran a piece of SQL today, it is not running. Is there something wrong in the syntax?

    I am baffled!

    BETWEEN to_date('01-APR-'||to_char(SYSDATE,'YYYY'),'DD-MON-YYYY') AND next_day( trunc(sysdate) - interval '7' day, 'SUN')

    The date I am basing this on is in this format: 2010-10-22 19:58:00 and I have chosen to chop off the time part to get the entire day

    Any ideas?

    Thanks
    Helen

  2. #2
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    What exactly does "it is not running" mean? I assume that means it is not giving the result you expect.

    Nothing wrong with the syntax, it compiles and it executes just fine. The logic on the other hand is no good.

    What does "to_date('01-APR-'||to_char(SYSDATE,'YYYY'),'DD-MON-YYYY')" give you if sysdate is 23-Feb-2013, for example. And what does "next_day( trunc(sysdate) - interval '7' day, 'SUN')" give you?
    Is there ever any value that can be BETWEEN these two?

    What you want is; if sysdate is before April, then financial year is LAST year April. The following DECODE statement will achieve that for you:
    Code:
    decode(to_char(sysdate, 'Q'), 
                  '1', trunc(add_months(sysdate, -9), 'Q'), 
                  add_months(trunc(sysdate, 'YYYY'), 3))
    Putting it all together:
    Code:
    dayneo@RMSD> select 'X' from dual
      2  where to_date('15/Jan/2013', 'dd/Mon/yyyy')
      3                between decode(to_char(sysdate, 'Q'),
      4                               '1', trunc(add_months(sysdate, -9), 'Q'),
      5                               add_months(trunc(sysdate, 'YYYY'), 3))
      6                    and trunc(next_day(sysdate - 7, 'SUN'))
      7  /
    
    '
    -
    X

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •