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********@netscape.net> wrote in message
news:6a**************************@posting.google.c om...
| 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********@netscape.net> wrote in message
news:6a**************************@posting.google.c om...
| 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********@netscape.net> wrote in message
news:c1**********@news4.tilbu1.nb.home.nl...
| Mark C. Stock wrote:
| > "Solomon Grundy" <lo********@netscape.net> wrote in message
| > news:6a**************************@posting.google.c om...
| > | 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********@netscape.net> wrote in message
news:c1**********@news4.tilbu1.nb.home.nl...
| Mark C. Stock wrote:
| > "Solomon Grundy" <lo********@netscape.net> wrote in message
| > news:6a**************************@posting.google.c om...
| > | 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********@netscape.net> wrote in message
news:6a**************************@posting.google.c om...

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

[Mark C. Stock]

"George Ziniewicz" <zi**@cox.net> wrote in message
news:ZoQ0c.5727$506.3314@fed1read05...
|
|
| "Solomon Grundy" <lo********@netscape.net> wrote in message
| news:6a**************************@posting.google.c om...
| > 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

[Mark C. Stock]

"Mark C. Stock" <mcstockX@Xenquery .com> wrote in message
news:To********************@comcast.com...
|
| "George Ziniewicz" <zi**@cox.net> wrote in message
| news:ZoQ0c.5727$506.3314@fed1read05...
| |
| |
| | "Solomon Grundy" <lo********@netscape.net> wrote in message
| | news:6a**************************@posting.google.c om...
| | > 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

[Mark D Powell]

"Mark C. Stock" <mcstockX@Xenquery .com> wrote in message news:<M6********************@comcast.com>...

"Mark C. Stock" <mcstockX@Xenquery .com> wrote in message
news:To********************@comcast.com...
|
| "George Ziniewicz" <zi**@cox.net> wrote in message
| news:ZoQ0c.5727$506.3314@fed1read05...
| |
| |
| | "Solomon Grundy" <lo********@netscape.net> wrote in message
| | news:6a**************************@posting.google.c om...
| | > 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(
---------
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********@netscape.net (Solomon Grundy) wrote in message news:<6a**************************@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?

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

Replace sysdate with any date.

[Mark C. Stock]

"Mark D Powell" <Ma*********@eds.com> wrote in message
news:26**************************@posting.google.c om...
| 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(
| ---------
| 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]

"Mark C. Stock" <mcstockX@Xenquery .com> wrote in message news:<V7********************@comcast.com>...

"Mark D Powell" <Ma*********@eds.com> wrote in message
news:26**************************@posting.google.c om...
| 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(
| ---------
| 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 --