Re: Need a DATE script that produces....

>I need a sql*plus script that will produce these results
>for a calendar year of 1999. I need the first day of the
>business week (Monday) and the last day of the week (Friday).
>I could create a table/view and hand load these in, but was
>wondering if anyone could come up with a script to generate
>these dates using sql*plus.
>
>If you create it, could you post it here? or give URL for the
>web site of where we could download it? Thanks!
>
>First Day of Week Last Day of Week
>----------------- -----------------
>04-JAN-1999 08-JAN-1999
>11-JAN-1999 15-JAN-1999
>18-JAN-1999 22-JAN-1999
>25-JAN-1999 29-JAN-1999
>01-FEB-1999 05-FEB-1999
>08-FEB-1999 12-FEB-1999
>
>
>
>

There is a format mask for dates that converts the date into the day of the week, where Sunday =0, Monday=1, etc (check the documentation on this,,,); I think it might be 'W'.

Subtract this from the test date to get the beginning of the week; something like

select test_date-to_number(to_char(test_date,'W')) from dual

To get the end of the week you have to see if your date is a Sat or Sun first, then add 5-(day of week number)

select test_date + decode(to_char(test_date,'W'),7,-2,6,-1, 5-to_char(test_date,'W')) from dual

(or you can just use a full decode...)

Hope this gets you started...

Dan Hekimian-Williams Received on Fri Feb 19 1999 - 04:09:43 CET