date diff for oracle 9i

[capik79]

Hi Guys,
I'm new in oracle... I'm using oracle in cf application now. Oracle 9i.
Now i want to display records for 6 months. How to use datediff function in oracle?
My query will like this
Select * from tblname
Where datediff(now(),fielddate) <= 6.

Any one use oracle? Please help me on this.

Thank in advance,
Regards

Shaffiq

[Dan_Bracuk]

Oracle does not have some of the date functions present in other db's. Two examples are datediff and dateadd.

They do have months_between. It might be appropriate in this situation.

Also, in Oracle, the current date and time is called sysdate, not now().

[kodemonki]

You might want to use "where fielddate > SYSDATE - 180, but 180 is just an approximation in days of 6 months and won't be exact. I use 8i, but aside from outer joins, they're very similar. I highly recommend this page: http://www.techonthenet.com/oracle/index.php

[paross1]

How about using the ADD_MONTHS() function?

Something like this?

SELECT *
FROM tblname
WHERE fielddate >= ADD_MONTHS(sysdate, -6)

--or--

SELECT *
FROM tblname
WHERE ADD_MONTHS(fielddate, 6) >= SYSDATE

Phil

[capik79]

Hi Guys,
Thanks for the solution. Great help. I use ADD_MONTHS(sysdate, -6) and it works great.

Thx VM

Regards,
Shaffiq