Re: I must be missing the obvious
Date: 1 Jul 2004 09:57:31 GMT
Message-ID: <20040701095732.GB23464_at_tormoz.net>
> 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')
---------------------------------------^ of course: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.
Received on Thu Jul 01 2004 - 11:57:31 CEST