DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: help with date (function) sql

  1. #1
    Join Date
    Apr 2007
    Location
    USA
    Posts
    110

    help with date (function) sql

    Hi,
    I need to copy data from my_table by identifying the records with column date_col01. date_col01 is the LAST DAY for every month (a date column)
    For example....on the 4th of the present month, copy records that came in on the LAST DAY of last month (identified by date_col01)

    P/S
    I don't want to hard code the date because this is a job to be setup to run every month (present month) looking back at the previous month.
    Thanks
    Looking for the greatest evil in the world? Look in the mirror.

  2. #2
    Join Date
    Mar 2000
    Location
    Atlanta, GA,USA
    Posts
    155
    Sounds like you need to use a combination of ADD_MONTHS and LAST_DAY functions. Something like LAST_DAY(ADD_MONTHS(date_col1,-1))

    Sergey

  3. #3
    Join Date
    Apr 2007
    Location
    USA
    Posts
    110
    pss,
    I think you are on the right track...but once I add the functions i don't get any data but of course the data is there when I hard code the date..
    SQL> select count(*) from my_table where date_col01
    2 = last_day(add_months(date_col01,-3));

    COUNT(*)
    ----------
    0

    SQL> select count(*) from my_table where date_col01 ='31-MAR-2008';

    COUNT(*)
    ----------
    35362
    p/s
    I'm doing (date_col01, -3) or month of march because last month as of now is march. Could the (=) sign be the issue?
    Looking for the greatest evil in the world? Look in the mirror.

  4. #4
    Join Date
    Mar 2000
    Location
    Atlanta, GA,USA
    Posts
    155
    You are applying the functions to your column value when it needs to be like this:

    where date_col01 = last_day(add_months(trunc(sysdate),-3));

    I added trunc on sysdate to trim out time from the current value.

    Sergey

  5. #5
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492

    Cool TRUNC() your dates

    You may also need TRUNC() on your dates:
    Code:
    where TRUNC(date_col01) = last_day(add_months(trunc(sysdate),-3));
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  6. #6
    Join Date
    Mar 2000
    Location
    Atlanta, GA,USA
    Posts
    155
    Quote Originally Posted by LKBrwn_DBA
    You may also need TRUNC() on your dates:
    Code:
    where TRUNC(date_col01) = last_day(add_months(trunc(sysdate),-3));
    Looks like he is getting results when running the query with literal value. In case date values are not truncated in the table I would go for >= and < combination instead to avoid type conversion.

    Sergey

  7. #7
    Join Date
    Apr 2007
    Location
    USA
    Posts
    110
    ...the trunc/sysdate suggestions did the trick.

    Thanks a bundle fellows....i appreciate it wholesale.
    Looking for the greatest evil in the world? Look in the mirror.

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