By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,086 Members | 1,919 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,086 IT Pros & Developers. It's quick & easy.

Finding first saturday of month

P: n/a
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?
Jul 19 '05 #1
Share this Question
Share on Google+
11 Replies


P: n/a
"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
Jul 19 '05 #2

P: n/a
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

Jul 19 '05 #3

P: n/a

"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
Jul 19 '05 #4

P: n/a
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

Jul 19 '05 #5

P: n/a


"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
Jul 19 '05 #6

P: n/a

"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
Jul 19 '05 #7

P: n/a

"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
Jul 19 '05 #8

P: n/a
"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 --
Jul 19 '05 #9

P: n/a
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.
Jul 19 '05 #10

P: n/a
"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
Jul 19 '05 #11

P: n/a
"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 --
Jul 19 '05 #12

This discussion thread is closed

Replies have been disabled for this discussion.