Dates Between

[strom99]

How do I write a sql statement that will only select a date range within the current month?

For example, I need to see all expected deliveries this month without having to change the SQL each month.


EXPC_DEL_DATE BETWEEN FirstDayofMonth AND LastDayOfMonth

Don't want to hard code date in each month like this..
EXPC_DEL_DATE BETWEEN '2002-04-01' AND '2002-04-30'

Thanks

 

[johannesd]

You should be able to use something like the following...

SELECT
cast(substr(date,1,8)||'01' as DATE FORMAT 'YYYY-MM-DD') AS Begin_of_month_Date
,CAST(substr(ADD_MONTHS (CAST (substr(date,1,8)||'15' AS DATE FORMAT 'YYYY-MM-DD'), 1),1,8)||'01' AS date format 'yyyy-mm-dd') - 1 AS End_of_Month_Date;

You should also be able to utilize the SYS_CALENDER view to calculate the BOM and EOM values of the current month if the SQL above looks a little messy for your taste...

Hope this works for you..

 

[BillDHS]

Although not pretty, you could use two views of the sys_calendar table as follows:

select d1.field1, d1.field2, c2.calendar_date

from dataTable d1, sys_calendar c1, sys_calendar c2

where c1.calendar_date = current_date
and c1.month_of_calendar = c2.month_of_calendar
and d1.datadate = c2.calendar_date ;