Re: I must be missing the obvious

From: Joe Powell <joe.powell_at_lmco.com>
Date: 1 Jul 2004 14:47:19 -0700
Message-ID: <deea9325.0407011347.79fa7882@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 - 16:47:19 CDT