Re: Strange behaviour with date fields

You could also use the wildcard operator: select count(*) from hits where stamp like '01-FEB-99%';

Graham C Thornton wrote:

> Clemens,
>
> Use this version.....
>
> SQL> select count(*) from hits where
> 2 trunc( stamp ) = to_date('01.02.1999', 'dd.mm.yyyy');
>
> Graham.
>
> Clemens Hoffmann wrote in message <7f45pp$a3j$1_at_linus.heeg.de>...
> >Hi there,
> >
> >maybe someone can explain this to me and give a hint how to solve it:
> >
> >I have a table for storing hit information from a web server log file:
> >
> >SQL> desc hits
> > Name Null? Typ
> > ------------------------------- -------- ----
> > HIT_ID NOT NULL NUMBER(38)
> > USER_ID
> NUMBER(38)
> > PAGE_ID
> NUMBER(38)
> > STAMP DATE
> > STATUS
> NUMBER(38)
> >
> >The filed stamp contains time stamps build from the date and
> >time the hit occured. When i select for a specific date i get the
> >following strange result:
> >
> >SQL> select count(*) from hits where
> > 2 (stamp = to_date('01.02.1999', 'dd.mm.yyyy'));
> >
> > COUNT(*)
> >---------------
> > 0
> >
> >SQL> select count(*) from hits where
> > 2 (stamp > to_date('01.02.1999', 'dd.mm.yyyy')) and
> > 3 (stamp < to_date('02.02.1999', 'dd.mm.yyyy'));
> >
> > COUNT(*)
> >---------------
> > 513
> >
> >SQL> select count(*) from hits where
> > 2 (stamp >= to_date('01.02.1999', 'dd.mm.yyyy')) and
> > 3 (stamp <= to_date('01.02.1999', 'dd.mm.yyyy'));
> >
> > COUNT(*)
> >---------------
> > 0
> >
> >I asume that the reason for this is the time information
> >in the stamp field.
> >
> >If yes, is there an Oracle SQL statment that i can use to
> >query using the '=' operator
> >
> >greetings
> >
> > Clemens
> >
> >
Received on Sun Apr 18 1999 - 11:23:45 CDT