Re: TRUNC function in SQL*Plus

At 08:49 AM 2/7/96 EST, you wrote:
>I am using TRUNC to try and get the Monday of a given week for any date I
>have in my table. TRUNC doesn't seem to be doing as advertised. Could
>anyone tell me if they get the same results, or if I'm reading the
>definiton of TRUNC wrong? Here's an example: select TRUNC (TO_DATE
>('17-FEB-94'), 'ww') from dual; gives me 12-FEB-94. 17-FEB-94 was a
>Thursday, so the answer should have been 14-FEB-94. Does anyone know what's
>going on here? - Jim
>

As I read the trunc function, trunc("date", 'ww') will return the day of the week in the current month that corresponds to the first day of the year. In your example the first day of 1994 was a Saturday, so trunc("date", ww) will return the last occurance of Saturday in the date you supply, or Saturday, February 12th 1994.

One way to get the Monday of a given week would be to do:

        next_day(to_date('17-FEB-94'), 'MONDAY') - 7

Get the date of the Monday following the date supplied and subtract 7 days from it.

Peter Brenner                                     Phone: (704) 329-5282
Coats American                                      Fax: (704) 329-5832
Charlotte, NC                                     Email: Peter.Brenner_at_coats.com