Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2009
    Location
    Montreal (Canada)
    Posts
    3

    Unanswered: Start-of-month & End-of-month

    Hi,

    First post here!
    Actually, I'm new to sybase but have lots of experience with Oracle.

    I am bugged with a query I'm trying to do.
    I would like to purge a table, but keep all the records based on the following conditionsbased on field 'date1')
    - I need to keep actual month;
    - I need to keep previous 2 months;
    - I need to keep every month end (carefull! 'date1' is not necessarily the 30th or 31st. for instance, in february 2009, month end would be 27th, the 28th is a Saturday)

    In Oracle, it would look like this:
    DELETE FROM table_abc
    WHERE date1 < ADD_MONTHS(TRUNC(sysdate,'MM'),-2)
    -- This removes records older than 2 months:

    and:
    SELECT MAX(date1)
    FROM table_abc
    GROUP BY TRUNC(date1,'MM')
    -- This gives me end of month for every month

    Can someone be kind enough to give me a Sybase translation of this?

    Thank you!

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Code:
    DELETE FROM table_abc
    WHERE date1 < ADD_MONTHS(TRUNC(sysdate,'MM'),-2)
    -- This removes records older than 2 months:
    becomes:
    Code:
    delete table_abc
    where date1 < dateadd( month,-2,convert(varchar,getdate(),112) )
    Code:
    SELECT MAX(date1)
    FROM table_abc
    GROUP BY TRUNC(date1,'MM')
    becomes:
    Code:
    select max(date1)
    from table_abc
    group by convert( char(7), date1, 102 )
    I don't have a Sybase nearby so this hasn't been tested but the above should be close. Remember to do a "begin transaction" before doing the delete, then check the data's ok before doing a "commit transaction". Alternatively you could change the "delete" to "select date1 from" to just check what dates it will delete. The date conversions used are listed here. The dateadd is explained here. The char(7) is used to strip the day of the month.

    Mike

Posting Permissions

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