This content has been marked as final. Show 7 replies
OrionNet wrote:Be careful! That returns the Saturday after today. If today happens to be a Saturday, (e.g., Jan. 17, 2009) that will be the end of the following week (Jan. 24).
This will work for you
select next_day(sysdate,'saturday') from dual;
Edited by: OrionNet on Jan 20, 2009 4:13 PM
NEXT_DAY (dt - 1, 'Saturday')
will return the Saturday on or after dt.
NEXT_DAY (dt, 'Sunday') - 1
Based on the input for all of you, this is the simple block I have created:
This just checks if the end of the week for the processing end date falls in next month , then dont use that week.
Thanks and Regards,
DECLARE lv_strt_dt DATE := TO_DATE ('01-Feb-2009'); lv_end_dt DATE := TO_DATE ('28-Feb-2009'); ld_start_cutoff_date DATE; ld_end_cutoff_date DATE; BEGIN ld_start_cutoff_date := TRUNC (lv_strt_dt, 'DAY'); IF TRUNC (TRUNC (lv_end_dt, 'DAY') + 6, 'MM') < > TRUNC (lv_end_dt, 'MM') THEN ld_end_cutoff_date := (TRUNC (lv_end_dt, 'DAY') - 1); ELSE ld_end_cutoff_date := (TRUNC (lv_end_dt, 'DAY') + 6); END IF; DBMS_OUTPUT.put_line (ld_start_cutoff_date||','||ld_end_cutoff_date); END;
Edited by: saffron on Jan 20, 2009 1:54 PM
Edited by: saffron on Jan 20, 2009 1:56 PM