Re: Finding first saturday of month

"Mark C. Stock" <mcstockX_at_Xenquery .com> wrote in message news:<V72dnYfQfNA-jtjdRVn-hA_at_comcast.com>...
> "Mark D Powell" <Mark.Powell_at_eds.com> wrote in message
> news:2687bb95.0403021110.473851c0_at_posting.google.com...
> | I believe the SQL posted earlier may have a problem if the first day
> | of the month is a Saturday as my test found the next Saturday in this
> | case. (May is the only month in 2004 that starts on a Saturday)
> |
> | Here is my attempt:
> | 1 select next_day(last_day(add_months(sysdate,-1)),'Saturday')
> | 2* from dual
> | _at_UT1 SQL>> /
> |
> | NEXT_DAY(
> | ---------
> | 06-MAR-04
> |
> | 1 row selected.
> |
> | If you substitute any date in May for sysdate it still produces
> | Saturday 1-May as the result.
> |
> | HTH -- Mark D Powell --
>
> good solution
>
> here's the one that i thought i had posted earlier and never did:
>
> select next_day(trunc(sysdate,'MM')-1,'sat') first_sat
> from dual
>
> which skins the same cat but with one less function; it also works for may:
>
> ;-{ mcs

Yes, the elimination of one more function should be beneficial whenever this has to be applied to thousands of rows. I will have to remember that you can format in the trunc and not just work with the results: date w/o time.

• Mark D Powell --