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
