Comparing dates using date function and arithmetic

[bryan.seaton@gmail.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_D ATE)- 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...rig ht? 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!

[Mark A]

Re: Comparing dates using date function and arithmetic

<bryan.seaton@g mail.com> wrote in message
news:1115656454 .147366.57300@f 14g2000cwb.goog legroups.com...[color=blue]
>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_D ATE)- 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...rig ht? 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!
>[/color]
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.

[bryan.seaton@gmail.com]

Re: Comparing dates using date function and arithmetic

>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.
[color=blue]
>It will run faster if you skip the conversion to DATE on both sides of[/color]
the
equation.

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

[Mark A]

Re: Comparing dates using date function and arithmetic

<bryan.seaton@g mail.com> wrote in message
news:1115657924 .997582.20550@f 14g2000cwb.goog legroups.com...[color=blue][color=green]
> >What is CURRENT_DATE? Shouldn't you be using CURRENT DATE?[/color]
>
> I questioned that as well but found examples using CURRENT_DATE and
> CURRENT DATE so I'm a little confused.
>[/color]
After checking the SQL Reference, CURRENT_DATE is also acceptable.
[color=blue][color=green]
>>It will run faster if you skip the conversion to DATE on both sides of[/color]
> the
> equation.
>
> So I don't need to convert the timestamp value to a date using the DATE
> function?
>[/color]
If you use (CURRENT TIMESTAMP - 21 days) it will work.

[Rhino]

Re: Comparing dates using date function and arithmetic


<bryan.seaton@g mail.com> wrote in message
news:1115657924 .997582.20550@f 14g2000cwb.goog legroups.com...[color=blue][color=green]
> >What is CURRENT_DATE? Shouldn't you be using CURRENT DATE?[/color]
>
> I questioned that as well but found examples using CURRENT_DATE and
> CURRENT DATE so I'm a little confused.
>[/color]
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.
[color=blue][color=green]
> >It will run faster if you skip the conversion to DATE on both sides of[/color]
> the
> equation.
>
> So I don't need to convert the timestamp value to a date using the DATE
> function?
>[/color]
I think Mark is suggesting you do this:

Delete from LOG_TABLE
where LOG_TS < CURRENT_TIMESTA MP - 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

[Mark A]

Re: Comparing dates using date function and arithmetic

"Rhino" <rhino1@NOSPAM. sympatico.ca> wrote in message news:VPNfe.1614 3[color=blue]
> 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.
>[/color]
The underscore version is for compliance with the the SQL 1999 Core
standard.

[Rhino]

Re: Comparing dates using date function and arithmetic


"Mark A" <nobody@nowhere .com> wrote in message
news:hsKdnZwrbp G5M-LfRVn-jg@comcast.com. ..[color=blue]
> "Rhino" <rhino1@NOSPAM. sympatico.ca> wrote in message news:VPNfe.1614 3[color=green]
> > 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[/color][/color]
a[color=blue][color=green]
> > 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.
> >[/color]
> The underscore version is for compliance with the the SQL 1999 Core
> standard.
>[/color]
Okay, now I know for sure ;-)

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

Rhino