I'm pretty sure that there's no such thing as the date without the time
part. To see the time part in WISQL, you have to turn the switch on to show
it.
Session -> Basic Settings -> Show Time DataType

My guess is that it's storing 00:00:00, for the time. That, more than
likely, explains some of what you're seeing.


Best regards,
Fred Wilson
SE, Bell & Howell
fred.wilson@... <mailto:fred.wilson@...>




-----Original Message-----
From: Bob Murdoch [mailto:ram5@...]
Sent: Tuesday, February 26, 2002 2:32 PM
To: ib-support@yahoogroups.com
Subject: Re: [ib-support] Strange query results, based on where clause


At 2/26/2002 03:55 PM, Ann wrote:

>At 02:53 PM 2/26/2002 -0500, Bob Murdoch wrote:
> First, in a dialect 1 database, the data datatype includes a time
>portion. You may have stored some value for time of day. That would
>explain why
> s.del_date between '2/20/2002'
> s.del_date between '2/20/2002' and '2/20/2002'
>don't work. Nothing happened at exactly the stroke of midnight between
>the 19th and the 20th. However, it doesn't explain why
>s.del_date between '2/19/2002' and '2/20/2002'
>returned values for the 20th.

Understood. I'm storing the value without the time part. In fact, in
WISQL, a query on that column returns 'mm/dd/yyyy ', not the
typical 'mm/dd/yyyy hh:mm:ss' it would if I had stored the time. I
understand what you are talking about here, and typically don't rely on a
direct comparison to the date in my apps, just in case.

> It should have returned values for everything starting at the midnight
> between the 18th and 19th, up to but not including
>things that started at the midnight between the 19th and 20th.
>
>Why changing something from
> s.account_id in (1)
>to
> s.account_id in (1,2)
>should have any effect I haven't a clue. Knowing all the datatypes -
>you're not using floating point for your account id are you? - might
>help. So would the plan generated for the query. Are the plans the same
>for s.account_id in (1) and s.account_id in (1,2)?

Account_id is an Integer. The plans for both queries are the same -

PLAN (S ORDER SALE_MDSE_RU_DATE_IDX)


The ddl for the table is:

/* Extract Table SALE_MDSE_RU */
/* Domain definitions */
CREATE DOMAIN D_DATE AS DATE;
CREATE DOMAIN D_ID AS INTEGER NOT NULL;
CREATE DOMAIN D_ID AS INTEGER NOT NULL;
CREATE DOMAIN D_DEPT AS CHAR(4);
CREATE DOMAIN D_INTEGER AS INTEGER;
CREATE DOMAIN D_SKU AS VARCHAR(20);
CREATE DOMAIN D_VENDOR AS CHAR(3);
CREATE DOMAIN D_YNFLAG AS CHAR(1);
CREATE DOMAIN D_MDSE_DESC AS VARCHAR(35);

/* Table: SALE_MDSE_RU, Owner: SYSDBA */
CREATE TABLE SALE_MDSE_RU (ID D_ID,
ACCOUNT_ID D_ID,
DEL_DATE D_DATE NOT NULL,
DEPT D_DEPT NOT NULL,
VENDOR D_VENDOR NOT NULL,
SKU D_SKU NOT NULL,
QTY D_INTEGER NOT NULL,
PICKED_FLAG D_YNFLAG,
STYLE D_MDSE_DESC,
CONSTRAINT PK_SALE_MDSE_RU PRIMARY KEY (ID));





Yahoo! Groups Sponsor

ADVERTISEMENT

<http://rd.yahoo.com/M=217097.1884387.3381019.1261774/D=egroupweb/S=17051153
86:HM/A=960173/R=0/*http://service.bfast.com/bfast/click?bfmid=29150849&site
id=39249818&bfpage=moneyyahoo4>

<http://us.adserver.yahoo.com/l?M=217097.1884387.3381019.1261774/D=egroupmai
l/S=1705115386:HM/A=960173/rand=228358370>

To unsubscribe from this group, send an email to:
ib-support-unsubscribe@egroups.com



Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service
<http://docs.yahoo.com/info/terms/> .