| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> comp.databases.oracle -> Re: Finding first saturday of month
"Mark C. Stock" <mcstockX_at_Xenquery .com> wrote in message news:<M6SdnQI098hadN7d4p2dnA_at_comcast.com>...
> "Mark C. Stock" <mcstockX_at_Xenquery .com> wrote in message
> news:ToCdnUKDFcjeed7dRVn-hw_at_comcast.com...
> |
> | "George Ziniewicz" <zin1_at_cox.net> wrote in message
> | news:ZoQ0c.5727$506.3314_at_fed1read05...
> | |
> | |
> | | "Solomon Grundy" <logicjello_at_netscape.net> wrote in message
> | | news:6ad4e542.0402271226.6f8d8e63_at_posting.google.com...
> | | > Select trunc(sysdate,'MM')
> | | > from dual
> | | > of course gives me the first day of the month, may I ask what the
> | | > query is to find the first saturday of the month?
> | |
> | | This appears to work for me:
> | |
> | | trunc(to_date(year||month),'D')+6 from dual
> | |
> | | zin
> | | --- http://www.zintel.com
> | |
> | |
> |
> | zin,
> |
> | you might want to try again (seriously, no sarcasm intended)
> |
> | this expression (even with the added SELECT) doesn't run, and if it did,
> | adding 6 days to a date value only returns a saturday if the date is known
> | to be a sunday -- so to use something like this to find the first saturday
> | of the month, you'd have to first find the last sunday of the prior month
> |
> | did you have something else in mind when you posted this?
> |
> | ;-{ mcs
> |
> |
>
> woops -- my bad
>
> spent a week working on MS-Access recently, so i mis-read the "D" format
>
> however, 'D' truncation to the first day of the week does not always return
> sunday -- it is dependent on the NLS_TERRITORY setting (then again, to a
> degree, so was my solution using the NEXT_DAY function)
>
> ;-{ mcs
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
@UT1 SQL>> /
NEXT_DAY(
If you substitute any date in May for sysdate it still produces Saturday 1-May as the result.
HTH -- Mark D Powell -- Received on Tue Mar 02 2004 - 13:10:10 CST
 |
 |