Re: I must be missing the obvious

Hello, Joe,

near 09:03 30-Jun from joe.powell_at_lmco.com accepted:

> We datestamp each record in table X with sysdate. In order to query
> all table X records in the previous month including its last second, I
> search between the first day of the last and current month. But for
> reports, I show the end date of the report as the last second of last
> month because humans think "from 1 to 30" not "between 1 and 31". So
> what is the standard for these queries?

There are no "standard" how you can see... The best practicies instead.

Assume

CREATE TABLE T1(d1 date, n1 number);

In order to receive previous month's data I'm using this statement

SELECT SUM(n1)
FROM T1
WHERE d1 >= trunc(add_months(sysdate,1),'month')

        AND d1 < trunc(sysdate, 'month');

Doing so you can get the data without thinking about measuring precision (second or fraction of it).

> I can to_char the datestamp--but that is very slow--and changing the
> datestamp's type to varchar2 is not possible nor am I sure desireable.

cast (if you really using timestamp datatype), to_char makes it possible (not desireable of course).

> Am I the only one with this question?

Anyone who makes reports has solved this question, I think.

-- 
wbr,
Wit.