Re: I must be missing the obvious
Date: 1 Jul 2004 14:47:19 -0700
Message-ID: <deea9325.0407011347.79fa7882_at_posting.google.com>
I appreciate your responses. I've used the following (I believe similar) clauses:
WHERE datestamp >= LAST_DAY(ADD_MONTHS(sysdate,-2))+1
AND datestamp < LAST_DAY(ADD_MONTHS(sysdate,-1))+1
Or (after altering the nls_date_format)
WHERE datestamp BETWEEN LAST_DAY(ADD_MONTHS(sysdate,-2))+1
AND LAST_DAY(ADD_MONTHS(sysdate,-1))||'235959'
Neither of these queries is particularly elegant in syntax, but they may be as good as it gets. I posted to find out alternative methods, particularly if some truly simple syntax is possible. I still don't understand the following. If I query:
'select max(datestamp)from X' with nls_date_format = 'DD-MON-YY'
I get today 30-JUN-04. If there are 10 records in X including 3 from
today, why does
'select count(1) from X where datestamp < '01-JUL-04' return 10 but
'select count(1) from X where datestamp <= '30-JUN-04' returns 7?
Adding a to_date() around the date or using "between" does not change the results. Again, thanks for your time. Received on Thu Jul 01 2004 - 23:47:19 CEST