JDCam
asked on
Oracle 10g Select older than current month
Experts,
I am trying to select records where the date is older than the current month. I am using this:
and MY_DATE <= ADD_MONTHS(TRUNC(SYSDATE),
but it is not working good. Seems to select records based on 30 days, and not the month.
For example on March 19, any record Feburary or older needs to be selected. The above is eliminating records in late Feb.
How do i do this properly?
I am trying to select records where the date is older than the current month. I am using this:
and MY_DATE <= ADD_MONTHS(TRUNC(SYSDATE),
but it is not working good. Seems to select records based on 30 days, and not the month.
For example on March 19, any record Feburary or older needs to be selected. The above is eliminating records in late Feb.
How do i do this properly?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
then try this
select add_months(trunc(to_date(S
select add_months(trunc(to_date(S
sorry... i was late...!! and didn't refresh my window...
wasimibm,
the asker already has the answer, but for your benefit and future readers,
This has multiple issues:
add_months(trunc(to_date(S
first - to_date(sysdate) - this doesn't make sense. SYSDATE is already a date, why would you implicitly convert it to a string and then explicitly convert it back to a date and without a format? At best it's inefficient, at worst it'll return a corrupted value without error, although it could also return an error and simply fail.
the trunc part is correct assuming you get a valid date out of the first part (not guaranteed though)
the add_months would eliminate all of the previous month as well as the current month.
So, in the asker's example, March sysdate should return February and older data. Using add_months on the already truncated value won't work though because that would return January and older data
the asker already has the answer, but for your benefit and future readers,
This has multiple issues:
add_months(trunc(to_date(S
first - to_date(sysdate) - this doesn't make sense. SYSDATE is already a date, why would you implicitly convert it to a string and then explicitly convert it back to a date and without a format? At best it's inefficient, at worst it'll return a corrupted value without error, although it could also return an error and simply fail.
the trunc part is correct assuming you get a valid date out of the first part (not guaranteed though)
the add_months would eliminate all of the previous month as well as the current month.
So, in the asker's example, March sysdate should return February and older data. Using add_months on the already truncated value won't work though because that would return January and older data
agreed sdstuber, realised it after it posted the first comment, but was unable to modify it as i had posted some other comment at the same time....
thanks for the clarification..!!!
thanks for the clarification..!!!
ASKER