Thread: why are these query results differing?

why are these query results differing?

From
micke
Date:
I changed a query to what I thought would be a more correct format. But
I'm not understanding the results, which are larger in quantity. I
thought this might be a last-date-not-inclusive problem, but when I
query on the last day alone, it doesn't account for the difference in
sum. On further tests with the query, I am beginning to become doubtful
that the date I'm providing is being read in DD-MM-YY format, even when
I specify this in the to_char () function. I don't trust the data any
more.

Another question I had is why to_date () works differently than to_char
(). Accourding to the documentation, to_date is the correct function to
use and to_char won't work with timestamps. But to_char is what everyone
uses. And, in fact, to_date doesn't work at all with timestamps in this
case, at least.

Help appreciated,
Micke

       SELECT
               p.project_name,
               sum(h.hours)
         FROM
               project p  LEFT OUTER JOIN hours h USING (project_id)
        WHERE
               (h.day >= '05-05-03' and h.day <= '09-05-03') or h.day =
NULL
     GROUP BY  p.project_name

               (h.day >= '05-05-03' and h.day <= '09-05-03') or h.day =
NULL
 changed to =>
               (to_char (h.day, 'dd-mm-yy') >= '05-05-03' and to_char
(h.day, 'dd-mm-yy') <= '09-05-03') or h.day = NULL