help with date (function) sql

[Tuma]

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

[pss]

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

[Tuma]

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?

[pss]

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

[LKBrwn_DBA]

You may also need TRUNC() on your dates:

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

[pss]

You may also need TRUNC() on your dates:

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

[Tuma]

...the trunc/sysdate suggestions did the trick.

Thanks a bundle fellows....i appreciate it wholesale.