Need Last Months Start and End Dates
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Need Last Months Start and End Dates

  1. #1
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346

    Need Last Months Start and End Dates

    Hi,

    Anyone have a simple solution to generate last months start and end dates ?

    For example, today is 12-Nov-2003, I need 01-Oct-2003,31-Oct-2003.

    If today is 01-Jan-2003, I need 01-Dec-2002,31-Dec-2002

    I've got a solution but its messy i.e., tochar(todate(tochar)) etc..

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Code:
    SQL> l
      1  select sysdate,
      2         trunc(sysdate,'MM')-1 end_last_month,
      3         trunc(trunc(sysdate,'MM')-1,'MM') start_last_month
      4* from dual
    SQL> /
    
    SYSDATE   END_LAST_ START_LAS
    --------- --------- ---------
    12-NOV-03 31-OCT-03 01-OCT-03
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    or better yet...

    Code:
    SQL> l
      1  select sysdate,
      2         last_day(add_months(sysdate, -1)) end_last_month,
      3         trunc(add_months(sysdate, -1), 'MM') start_last_month
      4* from dual
    SQL> /
    
    SYSDATE   END_LAST_ START_LAS
    --------- --------- ---------
    12-NOV-03 31-OCT-03 01-OCT-03
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  4. #4
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346
    Thanks Jeff.

    I had

    select '01'|| substr(to_char(to_date(to_char(sysdate,'mmyy'),'mmyyy')-1,'dd/mm/yyyy'),3) ,
    to_char(to_date(to_char(sysdate,'mmyy'),'mmyyy')-1,'dd/mm/yyyy')
    from dual

Posting Permissions

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


Click Here to Expand Forum to Full Width