date diff for oracle 9i

Forum|Forum|19 years ago
December 12, 2006
3 replies
1653 views

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

Database access

This topic has been closed for replies.

C

capik79Author

Inspiring

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

Thx VM

Regards,
Shaffiq

K

kodemonki

Inspiring

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

P

paross1

Participating Frequently

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

D

Dan_Bracuk

Inspiring

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().

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded