date difference in oracle

[capik79]

Hi Guys,
I need your help to solved this issue.
I'm using oracle 9i, problem now I want to select data that old 6 months from sysdate. How Can i do that? I have one field for date, his field can be use to diff the date from sysdate. I know how to do in sql server but not in oracle. Pls help me on this

Thank in advance

Regards,
Shaffiq

[chazzy]

date manipulation is easier in oracle :)
select sysdate-1 from dual; returns yesterday
select sysdate - 180 from dual; returns 180 days ago.

probably 183 would be the right # of days for you.

[capik79]

Hi Chazzy,
Thanks for the solution, but 180 still not exactly 6 months. Within 6 months we also got months that have more than 30 days right? How about month that 31 days. If in sql server we can user datediff(MM,Now(),datefield). So with this function we can select data that exactly more in 6months.

Thank in advance

Regards
Shaffiq

[russell]

SELECT DateAdd('M', -6, sysdate) FROM dual;

[chazzy]

SELECT DateAdd('M', -6, sysdate) FROM dual;
Hmm haven't seen that one before, don't have a system so can't verify it.

But I was thinking about this issue before - i have no idea what i was saying before.

You want something like add months


SELECT add_months(SYSDATE, -6) FROM dual;
This would, probably, subtract 6 months from the current date.

[russell]

didn't know there was an add_months function. of course u r the oracle guru, not me. :)

[capik79]

Hi Guys,

Thanks a lot for the solution. It work superbly now. I'm using add_months function. Thats what I'm looking for. Really great help from both of you guys. Thank VM

Regards,
Shaffiq

[chazzy]

russell:

hahaha very far from it.

oracle's generally limited in basic functionality. though, it does have a lot of material built into the default packages. most date manipulation i've ever needed to have to do can be summarized as


TRUNC(SYSDATE+n)


capik:

Good to hear, glad to know it worked for you!