Re: Date math

From: Jonathan Gennick <jonathan_at_gennick.com>
Date: Tue, 25 Jul 2000 21:36:09 -0400
Message-Id: <10569.112957@fatcity.com>

To get items older than 7 days, I would do:

select * from mr_purchased_items
where pi_order_date < (trunc(sysdate) - 7);

The TRUNC function gets rid of the time component of the date. The result of (trunc(sysdate)-7) is midnight 7 days ago. Today is the 25th, so (trunc(sysdate) - 7) works out to July 18, 2000 at 12:00 am. Use "<" to return rows dated prior to July 18. If you want to include July 18 rows in your result, then use the following:

select * from mr_purchased_items
where pi_order_date < (trunc(sysdate) - 6);

If the PI_ORDER_DATE column ever contains a time-of-day other than midnight, then your original query (below) probably isn't doing what you want it to do. For example:

SQL> alter session set nls_date_format =3D 'dd-Mon-yyyy hh:mi:ss pm';

Session altered.

SQL> select sysdate time_now from dual;

TIME_NOW

---

25-Jul-2000 09:33:51 pm

1 row selected.

SQL> select * from test;

PI_ORDER_DATE

---

25-Jun-2000 11:00:00 pm

1 row selected.

SQL> select * from test
  2 where pi_order_date <=3D add_months(sysdate,-1);

no rows selected

It's possible that all your PI_ORDER_DATE values are truncated to the day, but I personally don't like to depend on the data being like that when I write a date query.

Jonathan

---

jonathan_at_gennick.com =20
http://gennick.com
Brighten the Corner Where You Are

On Tue, 25 Jul 2000 16:55:37 -0800, you wrote:

>The following SQL is selecting data older than one month ago. I need to
>change it to select data older than 7 days ago. Does anyone know the
>TO_CHAR(TO_DATE(SYSDATE etc. syntax to calculate a date of seven =
days
>ago?
>
> SELECT * FROM MR_PURCHASED_ITEMS
> WHERE PI_ORDER_DATE <=3D ADD_MONTHS(SYSDATE,-1) ;
>
>Any information is appreciated.=20
Received on Tue Jul 25 2000 - 20:36:09 CDT