By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,018 Members | 930 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,018 IT Pros & Developers. It's quick & easy.

Comparing dates using date function and arithmetic

P: n/a
I have a delete statement that is not doing what I want it to do:

Delete from LOG_TABLE where (DATE(LOG_TS)) < (DATE(CURRENT_DATE)- 21
DAYS);

It is supposed to delete all records that are 21 days or older than the
current system date. Instead it is deleting all new rows. LOG_TS is a
timestamp but that should not matter since DATE returns just the date
portion of a date or timestamp...right? I'm not a SQL guru but I can't
see anything wrong with this. I've looked thru the groups and checked
the IBM website but I can't find anything that addresses something like
this. Help!

Nov 12 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
<br**********@gmail.com> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.com...
I have a delete statement that is not doing what I want it to do:

Delete from LOG_TABLE where (DATE(LOG_TS)) < (DATE(CURRENT_DATE)- 21
DAYS);

It is supposed to delete all records that are 21 days or older than the
current system date. Instead it is deleting all new rows. LOG_TS is a
timestamp but that should not matter since DATE returns just the date
portion of a date or timestamp...right? I'm not a SQL guru but I can't
see anything wrong with this. I've looked thru the groups and checked
the IBM website but I can't find anything that addresses something like
this. Help!

What is CURRENT_DATE? Shouldn't you be using CURRENT DATE?

It will run faster if you skip the conversion to DATE on both sides of the
equation.
Nov 12 '05 #2

P: n/a
>What is CURRENT_DATE? Shouldn't you be using CURRENT DATE?

I questioned that as well but found examples using CURRENT_DATE and
CURRENT DATE so I'm a little confused.
It will run faster if you skip the conversion to DATE on both sides of

the
equation.

So I don't need to convert the timestamp value to a date using the DATE
function?

Nov 12 '05 #3

P: n/a
<br**********@gmail.com> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.com...
What is CURRENT_DATE? Shouldn't you be using CURRENT DATE?


I questioned that as well but found examples using CURRENT_DATE and
CURRENT DATE so I'm a little confused.

After checking the SQL Reference, CURRENT_DATE is also acceptable.
It will run faster if you skip the conversion to DATE on both sides of

the
equation.

So I don't need to convert the timestamp value to a date using the DATE
function?

If you use (CURRENT TIMESTAMP - 21 days) it will work.
Nov 12 '05 #4

P: n/a

<br**********@gmail.com> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.com...
What is CURRENT_DATE? Shouldn't you be using CURRENT DATE?


I questioned that as well but found examples using CURRENT_DATE and
CURRENT DATE so I'm a little confused.

Traditionally, DB2 on OS/390 and Windows/Linux/Unix used 'CURRENT DATE' (no
underscore) but a few versions back, some of the platforms and versions
started accepting 'CURRENT_DATE' (with underscore). Perhaps this is some
kind of a standards comformance thing or an attempt to make DB2's syntax a
little closer to that of some competitor; I really don't know. In any case,
I suspect that most current platforms and versions support both formats and
that they are completely interchangeable. You haven't stated which platform
and version you are using so I can only suggest that you check your SQL
Reference to see which format(s) will work on your system.
It will run faster if you skip the conversion to DATE on both sides of

the
equation.

So I don't need to convert the timestamp value to a date using the DATE
function?

I think Mark is suggesting you do this:

Delete from LOG_TABLE
where LOG_TS < CURRENT_TIMESTAMP - 21 DAYS;

On any reasonably current version of DB2 on any platform, you should be able
to subtract 21 days from a timestamp without difficulty. Then you avoid
having the date() scalar function on both sides of the predicate which, as
Mark says, should give you better performance.

For what it's worth, I wrote this little script and executed it on my system
(DB2 UDB V8.2 FP8 on Windows XP) and it worked fine; the only three records
returned by the select were the ones that were 21 days old or older:

----------------------------------------------------------------------------
---
drop table log_table;
create table log_table
(log_ts timestamp not null,
entry char(20) not null,
primary key(log_ts));

insert into log_table values
(current timestamp + 3 days, '3 days from now'),
(current timestamp + 2 days, '2 days from now'),
(current timestamp + 1 day, 'tomorrow'),
(current timestamp, 'today'),
(current timestamp - 3 days, '3 days ago'),
(current timestamp - 7 days, '7 days ago'),
(current timestamp - 14 days, '14 days ago'),
(current timestamp - 21 days, '21 days ago'),
(current timestamp - 22 days, '22 days ago'),
(current timestamp - 24 days, '24 days ago');

select * from log_table;

select * from log_table
where log_ts < current timestamp - 21 days;
----------------------------------------------------------------------------
---

The only reason I can see for you getting only newer records is that your
operator is the reverse of the one you've shown in your question, which
would be a simple typo/transcription error.

Rhino
Nov 12 '05 #5

P: n/a
"Rhino" <rh****@NOSPAM.sympatico.ca> wrote in message news:VPNfe.16143
Traditionally, DB2 on OS/390 and Windows/Linux/Unix used 'CURRENT DATE'
(no
underscore) but a few versions back, some of the platforms and versions
started accepting 'CURRENT_DATE' (with underscore). Perhaps this is some
kind of a standards comformance thing or an attempt to make DB2's syntax a
little closer to that of some competitor; I really don't know. In any
case,
I suspect that most current platforms and versions support both formats
and
that they are completely interchangeable. You haven't stated which
platform
and version you are using so I can only suggest that you check your SQL
Reference to see which format(s) will work on your system.

The underscore version is for compliance with the the SQL 1999 Core
standard.
Nov 12 '05 #6

P: n/a

"Mark A" <no****@nowhere.com> wrote in message
news:hs********************@comcast.com...
"Rhino" <rh****@NOSPAM.sympatico.ca> wrote in message news:VPNfe.16143
Traditionally, DB2 on OS/390 and Windows/Linux/Unix used 'CURRENT DATE'
(no
underscore) but a few versions back, some of the platforms and versions
started accepting 'CURRENT_DATE' (with underscore). Perhaps this is some
kind of a standards comformance thing or an attempt to make DB2's syntax a little closer to that of some competitor; I really don't know. In any
case,
I suspect that most current platforms and versions support both formats
and
that they are completely interchangeable. You haven't stated which
platform
and version you are using so I can only suggest that you check your SQL
Reference to see which format(s) will work on your system.

The underscore version is for compliance with the the SQL 1999 Core
standard.

Okay, now I know for sure ;-)

Thanks; I don't really follow the various standards very closely.

Rhino
Nov 12 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.