Hi, using Oracle 11g R2.
On the last day of each month of each year, I'd like to query data from a table over the last month. For example, if today is December 31st, 2013, I'd like to query only records with a settlement_dt between (and including) Dec 1, 2012 and Dec 31, 2012. My process will run automatically at the end of each month, so I can't hard code the date values. I've developed the following solution, but I was wondering if anyone had other ideas.
TO_CHAR(io.settlement_dt, 'MON')||TO_CHAR(io.settlement_dt, 'YYYY') =
TO_CHAR(SYSDATE, 'MON')||TO_CHAR(SYSDATE, 'YYYY')
The most efficient way is
io.settlement_dt >= TRUNC (SYSDATE, 'MONTH')
AND io_settlement_dt < ADD_MONTHS (TRUNC (SYSDATE, 'MONTH'), 1)
This will allow the optimizer to use an index on io.settlement_dt. Also, it involves only 3 function calls, regardless of how many rows are in the table (that is, no function has to be called for every row).