Re: Strange behaviour with date fields

Hi
its quit simple:
to_date('01.02.1999', 'dd.mm.yyyy') generates the date 01.02.1999 00:00:00 this shoul explain the behavior of your select statements

There are two ways to solve your problem I guess: a) trunc(stamp) = to_date('01.02.1999', 'dd.mm.yyyy') I'm not sure if trunc needs a nother parameter to specify the position of truncation

b) to_char(stamp, 'dd.mm.yyyy') = '01.02.1999'

Jens

Clemens Hoffmann <clemens.hoffmann_at_heeg.de> wrote in article <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 Thu Apr 15 1999 - 05:26:00 CDT