Finding first saturday of month

**Mark C. Stock** · Jun 27 '08, 06:02 PM

Re: Finding first saturday of month

"Solomon Grundy" <logicjello@net scape.netwrote in message
news:6ad4e542.0 402271226.6f8d8 e63@posting.goo gle.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?

check out the NEXT_DAY function

;-{ mcs

**Frank van Bortel** · Jun 27 '08, 06:02 PM

Re: Finding first saturday of month

Mark C. Stock wrote:

"Solomon Grundy" <logicjello@net scape.netwrote in message
news:6ad4e542.0 402271226.6f8d8 e63@posting.goo gle.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?
>
check out the NEXT_DAY function
>
;-{ mcs
>
>

And be careful with trunc(sysdate):
SQLselect trunc(sysdate,' MM') from dual;

TRUNC(SYSDATE,'
---------------
01-FEB-04

--

Regards,
Frank van Bortel

**Mark C. Stock** · Jun 27 '08, 06:02 PM

Re: Finding first saturday of month

"Frank van Bortel" <fvanbortel@net scape.netwrote in message
news:c1q0ck$8lk $1@news4.tilbu1 .nb.home.nl...
| Mark C. Stock wrote:
| "Solomon Grundy" <logicjello@net scape.netwrote in message
| news:6ad4e542.0 402271226.6f8d8 e63@posting.goo gle.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?
| >
| check out the NEXT_DAY function
| >
| ;-{ mcs
| >
| >
|
| And be careful with trunc(sysdate):
| SQLselect trunc(sysdate,' MM') from dual;
|
| TRUNC(SYSDATE,'
| ---------------
| 01-FEB-04
|
| --
|
| Regards,
| Frank van Bortel
|

frank,

the OP was intending to use trunc(sysdate,' MM'), which reliably returns the
first day of the current month -- in what way are you suggesting to be
careful with trunc(sysdate)?

;-{ mcs

**Frank van Bortel** · Jun 27 '08, 06:02 PM

Re: Finding first saturday of month

Mark C. Stock wrote:

"Frank van Bortel" <fvanbortel@net scape.netwrote in message
news:c1q0ck$8lk $1@news4.tilbu1 .nb.home.nl...
| Mark C. Stock wrote:
| "Solomon Grundy" <logicjello@net scape.netwrote in message
| news:6ad4e542.0 402271226.6f8d8 e63@posting.goo gle.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?
| >
| check out the NEXT_DAY function
| >
| ;-{ mcs
| >
| >
|
| And be careful with trunc(sysdate):
| SQLselect trunc(sysdate,' MM') from dual;
|
| TRUNC(SYSDATE,'
| ---------------
| 01-FEB-04
|
| --
|
| Regards,
| Frank van Bortel
|
>
frank,
>
the OP was intending to use trunc(sysdate,' MM'), which reliably returns the
first day of the current month -- in what way are you suggesting to be
careful with trunc(sysdate)?
>
;-{ mcs
>
>

Well, for one, I got my wires crossed.
Apologies, forget about previous posting of mine.
OP is correct
--

Regards,
Frank van Bortel

**George Ziniewicz** · Jun 27 '08, 06:03 PM

Re: Finding first saturday of month

"Solomon Grundy" <logicjello@net scape.netwrote in message
news:6ad4e542.0 402271226.6f8d8 e63@posting.goo gle.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(y ear||month),'D' )+6 from dual

zin
--- http://www.zintel.com

**Mark C. Stock** · Jun 27 '08, 06:03 PM

Re: Finding first saturday of month

"George Ziniewicz" <zin1@cox.netwr ote in message
news:ZoQ0c.5727 $506.3314@fed1r ead05...
|
|
| "Solomon Grundy" <logicjello@net scape.netwrote in message
| news:6ad4e542.0 402271226.6f8d8 e63@posting.goo gle.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(y ear||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

**Mark C. Stock** · Jun 27 '08, 06:03 PM

Re: Finding first saturday of month

"Mark C. Stock" <mcstockX@Xenqu ery .comwrote in message
news:ToCdnUKDFc jeed7dRVn-hw@comcast.com. ..
|
| "George Ziniewicz" <zin1@cox.netwr ote in message
| news:ZoQ0c.5727 $506.3314@fed1r ead05...
| |
| |
| | "Solomon Grundy" <logicjello@net scape.netwrote in message
| | news:6ad4e542.0 402271226.6f8d8 e63@posting.goo gle.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(y ear||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

**Mark D Powell** · Jun 27 '08, 06:03 PM

Re: Finding first saturday of month

"Mark C. Stock" <mcstockX@Xenqu ery .comwrote in message news:<M6SdnQI09 8hadN7d4p2dnA@c omcast.com>...

"Mark C. Stock" <mcstockX@Xenqu ery .comwrote in message
news:ToCdnUKDFc jeed7dRVn-hw@comcast.com. ..
|
| "George Ziniewicz" <zin1@cox.netwr ote in message
| news:ZoQ0c.5727 $506.3314@fed1r ead05...
| |
| |
| | "Solomon Grundy" <logicjello@net scape.netwrote in message
| | news:6ad4e542.0 402271226.6f8d8 e63@posting.goo gle.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(y ear||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_d ay(add_months(s ysdate,-1)),'Saturday')
2* from dual
@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 --

**Bill Smith** · Jun 27 '08, 06:03 PM

Re: Finding first saturday of month

logicjello@nets cape.net (Solomon Grundy) wrote in message news:<6ad4e542. 0402271226.6f8d 8e63@posting.go ogle.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?

Try this
select trunc(sysdate,' mm')+(7- to_char(trunc(s ysdate,'mm'),'d ')) first_sat
from dual;

Replace sysdate with any date.

**Mark C. Stock** · Jun 27 '08, 06:03 PM

Re: Finding first saturday of month

"Mark D Powell" <Mark.Powell@ed s.comwrote in message
news:2687bb95.0 403021110.47385 1c0@posting.goo gle.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_d ay(add_months(s ysdate,-1)),'Saturday')
| 2* from dual
| @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

**Mark D Powell** · Jun 27 '08, 06:04 PM

Re: Finding first saturday of month

"Mark C. Stock" <mcstockX@Xenqu ery .comwrote in message news:<V72dnYfQf NA-jtjdRVn-hA@comcast.com> ...

"Mark D Powell" <Mark.Powell@ed s.comwrote in message
news:2687bb95.0 403021110.47385 1c0@posting.goo gle.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_d ay(add_months(s ysdate,-1)),'Saturday')
| 2* from dual
| @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 --

Finding first saturday of month

Finding first saturday of month

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment