Finding first saturday of month

[Solomon Grundy]

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?

[Mark C. Stock]

"Solomon Grundy" <lo********@net scape.net> wrote in message
news:6a******** *************** ***@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]

Mark C. Stock wrote:

"Solomon Grundy" <lo********@net scape.net> wrote in message
news:6a******** *************** ***@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):
SQL> select trunc(sysdate,' MM') from dual;

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

--

Regards,
Frank van Bortel

[Mark C. Stock]

"Frank van Bortel" <fv********@net scape.net> wrote in message
news:c1******** **@news4.tilbu1 .nb.home.nl...
| Mark C. Stock wrote:
| > "Solomon Grundy" <lo********@net scape.net> wrote in message
| > news:6a******** *************** ***@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):
| SQL> select 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]

Mark C. Stock wrote:

"Frank van Bortel" <fv********@net scape.net> wrote in message
news:c1******** **@news4.tilbu1 .nb.home.nl...
| Mark C. Stock wrote:
| > "Solomon Grundy" <lo********@net scape.net> wrote in message
| > news:6a******** *************** ***@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):
| SQL> select 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]

"Solomon Grundy" <lo********@net scape.net> wrote in message
news:6a******** *************** ***@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]

"George Ziniewicz" <zi**@cox.net > wrote in message
news:ZoQ0c.5727 $506.3314@fed1r ead05...
|
|
| "Solomon Grundy" <lo********@net scape.net> wrote in message
| news:6a******** *************** ***@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]

"Mark C. Stock" <mcstockX@Xenqu ery .com> wrote in message
news:To******** ************@co mcast.com...
|
| "George Ziniewicz" <zi**@cox.net > wrote in message
| news:ZoQ0c.5727 $506.3314@fed1r ead05...
| |
| |
| | "Solomon Grundy" <lo********@net scape.net> wrote in message
| | news:6a******** *************** ***@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]

"Mark C. Stock" <mcstockX@Xenqu ery .com> wrote in message news:<M6******* *************@c omcast.com>...

"Mark C. Stock" <mcstockX@Xenqu ery .com> wrote in message
news:To******** ************@co mcast.com...
|
| "George Ziniewicz" <zi**@cox.net > wrote in message
| news:ZoQ0c.5727 $506.3314@fed1r ead05...
| |
| |
| | "Solomon Grundy" <lo********@net scape.net> wrote in message
| | news:6a******** *************** ***@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]

lo********@nets cape.net (Solomon Grundy) wrote in message news:<6a******* *************** ****@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.