Re: I must be missing the obvious
Date: 1 Jul 2004 06:36:58 -0700
Message-ID: <2687bb95.0407010536.2303d87c_at_posting.google.com>
D Rolfe <dwrolfeFRUITBAT_at_orindasoft.com> wrote in message news:<40E3BC89.4000605_at_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
David, apparently I did not make my intent clear. I am suggesting that the add_month and last_date functions could be substituted into the code to calculate the prior month off of sysdate so that code changes would not be required. Just run the code in the current month and all the rows for the prior month would be targeted.
Also if you want all the rows for a month you can ignore the time component of a date column if you target the month component of the date column. Example:
1 select fld1, fld2, to_char(fld3,'YYYYMMDD HH24:MI:SS') fld3, fld4
2* from marktest
UT1 > /
FLD1 FLD2 FLD3 FLD4
---------- ---------- ----------------- -------- one 1 20040601 00:00:00 one two 1 20040615 00:00:00 two three 1 20040630 00:00:00 three four 1 20040701 00:00:00 four five 5 20040531 23:59:59 five
1 select * from marktest
2* where to_char(fld3,'MON') =
to_char(trunc(add_months(sysdate,-1)),'MON')
UT1 > /
FLD1 FLD2 FLD3 FLD4
---------- ---------- --------- -------- one 1 01-JUN-04 one two 1 15-JUN-04 two three 1 30-JUN-04 three
If the date column is indexed I would not use this last method but would place the functions to the right of the relational operators on the input variables which I am suggesting is just add_months(trunc(sysdate), -1), i.e., first of prior month.
1 select to_char(add_months(trunc(sysdate), -1),'YYYYMMDD
HH24:MI:SS')
2* ,sysdate from dual
UT1 > /
TO_CHAR(ADD_MONTH SYSDATE
----------------- ---------
20040601 00:00:00 01-JUL-04 You can then use < the first of the current month, i.e., trunc(sysdate) and the time component is covered.
HTH -- Mark D Powell -- Received on Thu Jul 01 2004 - 15:36:58 CEST