Static date range every Month needed using YYYYMMDD Varchar 'date' format
Posted on 2008-10-14
I have an indexed CHAR field that is fully populated in the YYYYMMDD format and is used as the basic date range for reports. So, "select a,b,c from tablex where dt_field >= '20081014' " would retrieve rows from 10/14/2008, and so on. For Ongoing reports for last Month we use this format: dt_field BETWEEN to_char(last_day(add_months(sysdate, -2)) + 1,'YYYYMMDD') AND to_char(last_day(add_months(sysdate, -1)),'YYYYMMDD') ) . That Works fine.
But I have an ongoing request to view data FROM the 21st of the PREVIOUS Month to the 20th of the CURRENT month (i.e 10/21/08- 10/20/08 but for every month). The idea is to have some sort of flexible hardcode to work with. The field is an indexed field but I can use another field to subset the query in the where clause to speed things up a bit.
Thanks very Much,