SELECT * FROM t , (SELECT TO_CHAR(DECODE( LENGTH(p) , 10, TO_DATE(p, 'yyyy-mm-dd') , 9, TO_DATE(SUBSTR(p, 1, 7), 'yyyy-mm') + SUBSTR(p, -1)*7-7 , 7, TO_DATE(p, 'yyyy-mm') ), 'yyyymmdd') sdt , TO_CHAR(DECODE( LENGTH(p) , 10, TO_DATE(p, 'yyyy-mm-dd') , 9, TO_DATE(SUBSTR(p, 1, 7), 'yyyy-mm') + SUBSTR(p, -1)*7-1 , 7, LAST_DAY(TO_DATE(p, 'yyyy-mm')) ), 'yyyymmdd') edt FROM (SELECT :v_param AS p FROM dual) ) WHERE cnrtdt BETWEEN sdt AND edt ;