Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2005
    Posts
    1

    Thumbs up Unanswered: previous quarter dates

    Is there a built in function to get the previous quarter starting and ending dates ?
    if not can someone tell me how to get it. ?

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    if you are using standard quarters, yes.
    do a search on this forum and you should come up with a few discussions.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    I dont know if they are any built in functions, but you could generate them with a query like the following..
    Code:
    SQL@8i> variable thedate varchar2(15)
    SQL@8i>
    SQL@8i> exec :thedate := to_char( sysdate, 'dd-mm-yyyy' );
    
    PL/SQL procedure successfully completed.
    
    SQL@8i> select to_char( to_date( :thedate, 'dd-mm-yyyy' ), 'Q' ) actual_q,
      2         q prev_q,
      3         mind,
      4         maxd
      5    from (
      6  select q, min( d ) mind, max( d ) maxd
      7    from (
      8  select d + rownum d, to_char( d + rownum, 'Q' ) q
      9    from (
     10  select trunc( sysdate, 'YY' ) - 1 d
     11    from dual
     12   group by cube( 1, 1, 1, 1, 1, 1, 1, 1, 1 )
     13         )
     14   where rownum <= to_number( to_char( last_day( add_months( trunc( sysdate, 'YY' ), 11 ) ), 'DDD' ) )
     15         )
     16   group by q
     17         ) x
     18   where to_number( to_char( add_months( to_date( :thedate, 'dd-mm-yyyy' ), -3 ), 'Q' ) ) = q
     19  /
    
    A P MIND      MAXD
    - - --------- ---------
    1 4 01-OCT-05 31-DEC-05
    
    SQL@8i>
    P.S.: the to_number( to_char( last_day( add_months( trunc( sysdate, 'YY' ), 11 ) ), 'DDD' ) ) part is included for leap years, you could easily replace that with 365 ( or 366 for leaps )..

Posting Permissions

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