473,903 Members | 2,996 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Comparing dates using date function and arithmetic

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!

Nov 12 '05 #1
6 39861
<br**********@g mail.com> wrote in message
news:11******** *************@f 14g2000cwb.goog legroups.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!

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
>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
<br**********@g mail.com> wrote in message
news:11******** *************@f 14g2000cwb.goog legroups.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

<br**********@g mail.com> wrote in message
news:11******** *************@f 14g2000cwb.goog legroups.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_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
Nov 12 '05 #5
"Rhino" <rh****@NOSPAM. sympatico.ca> wrote in message news:VPNfe.1614 3
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

"Mark A" <no****@nowhere .com> wrote in message
news:hs******** ************@co mcast.com...
"Rhino" <rh****@NOSPAM. sympatico.ca> wrote in message news:VPNfe.1614 3
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

10
3303
by: Colin Steadman | last post by:
I'm a stupid ASP programmer and I dont do Javascript (except for very simple tasks anyway), and I'm in a bit of a predicament. I've used a javascript table sorting script from here: http://www.ipwebdesign.net/kaelisSpace/useful_tableSort.html This works great except it doesn't sort my UK formatted dates properly, and I end up with something like this: Birth Date (dd/mm/yyyy)
4
16816
by: Matt | last post by:
Hello. I have an Informix SQL statement that I need to run in MS SQL Server. When I try to execute it I get the following error message from Query Analyzer: Server: Msg 195, Level 15, State 10, Line 4 'date' is not a recognized function name. Can anyone help me convert this informix sql statement into and MS Sql Statement? Here is the query I have:
8
3035
by: Dennis M. Marks | last post by:
What is the maximum valid date range for the date(yyyy,mm,dd) function. -- Dennis M. Marks http://www.dcs-chico.com/~denmarks/ Replace domain.invalid with dcsi.net -----= Posted via Newsfeeds.Com, Uncensored Usenet News =----- http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
5
2440
by: Dennis M. Marks | last post by:
After reading section 15.9.1.1 the ECMAScript Language Specifications I see that the date range for the Date function is +/- 100,000,000 days from 01 Jan 1970. This is called an extrapolated Gregorian calendar. Since the Gregorian calendar did not begin until 15 Oct 1582 what is the purpose of dates before that date? Wouldn't any computation prior to that date be meaningless or am I missing something? The reason I ask is that I have...
6
2471
by: Bill R via AccessMonster.com | last post by:
I have a query: SELECT tblCalendar.CalendarDay AS LastSunday FROM tblCalendar WHERE (((tblCalendar.CalendarDay)>=(Now()-7) And (tblCalendar.CalendarDay) <DateAdd("d",8-Weekday((Now()-7),2),(Now()-7))) AND ((tblCalendar.Weekday)=1)) ; tblCalendar is a table of consecutive dates from 1998 thru 2020. It has proven useful in many applications. CalendarDay is the date. The code above
4
2359
by: cheryl | last post by:
I am using the PHP.MYSQL and Apache server application in developing my website. I have problem in comparing dates. Website has room reservation, the user will check first the room availability. The user will input dates using the combo box like cboyear,cboday and cbomonth. What SQL statement or quey should I write to compare it to the database. The database has already have value in dates. I want to compare the input date to the database.......
4
3067
by: OzNet | last post by:
I have some functions to calculate the working days in a given period. This includes a table that is queried to calculate the number of public holidays that don’t occur on a weekend. If I test the function using the intermediate window, it works fine. However, when I pass the dates from the code attached to my form, the results are inaccurate. You will notice my dates are in Australian format. Everything works fine using the Australian...
1
3340
by: sevak316 | last post by:
Hi, I would like to compare 2 dates and see which one is older using XQuery. Anyone? -Thanks.
2
1843
by: dragrid | last post by:
Hi anyone - appreciate any help I would like to get * one column * with that min of the day instead of hr and min separately So the first line for example 18:50 pm would be the 1130 min of the day And if it was 6:50 am would be 410 min of a day What is the best function I can use in my select statement to get this and be able to group by it ? select datepart(yyyy,ATIME)as yr, datepart(mm,ATIME)as mth, datepart(dd,ATIME)as daday...
0
9851
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10882
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10988
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10504
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9692
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
8055
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7213
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5897
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
2
4312
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.