Re: I must be missing the obvious

From: D Rolfe <dwrolfeFRUITBAT_at_orindasoft.com>
Date: Thu, 01 Jul 2004 08:26:01 +0100
Message-ID: <40E3BC89.4000605@orindasoft.com>

Mark D Powell wrote:

> D Rolfe <dwrolfeFRUITBAT_at_orindasoft.com> wrote in message news:<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

> 
> 
> I would think that you might want to look at the add_month and
> last_day date functions which would allow you to calculate the
> previous month from the sysdata and determine the last day of that
> month.  A trunc of the add_months(sysdate, -1) would give you the
> first day of the prior month.
> 

I am working on the assumption he wants a month that starts at:

01-Jun-04 00:00:00

and

30-Jun-04 23:59:59

He also mentioned that SYSDATE is used to populate the column, which means the DATE will be accurate to one second.

Because the BETWEEN operator is inclusive the search expression needs to   allow for the fact that the reporting period ends at 23:59:59. This means that the end date must exclude '01-Jul-04 00:00:00'. This implies working in seconds. You can either:

1. Use an expression that substracts 1 second from the end date
2. Use to_date and '23:59:59' to figure out the exact time the reporting period ends
3. Use BETWEEN and have an additional '<' condition to exclude the first second of the next month.

Failure to account for seconds will lead to reports that count transactions at midnight on the first day of a month as being in two seperate months. This can harm your end of year bonus.

David Rolfe
Orinda Software
Dublin, Ireland Received on Thu Jul 01 2004 - 02:26:01 CDT