Re: I must be missing the obvious
Date: Wed, 30 Jun 2004 17:11:43 +0100
Message-ID: <40E2E63F.9090805_at_orindasoft.com>
Joe Powell wrote:
> 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? 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. Am I the only one
> with this question?
To get all the records in one month you can say:
WHERE a_date
BETWEEN TO_DATE('01-Jun-2004','DD-MON-YYYY')
AND (TO_DATE('01-Jul-2004','DD-MON-YYYY') - (1/(24 * 60 * 60)))
1/(24 * 60 * 60) = 1 second if you are an oracle DATE column. 24 = hours in day and '60 * 60' = seconds in hour.
There is no reason why indexes won't work in this situation and no requirement to use to_char.
David Rolfe
Orinda Software
Dublin, Ireland
Orinda Software make OrindaBuild, A Java JDBC Code Generator www.orindasoft.com Received on Wed Jun 30 2004 - 18:11:43 CEST