Simple Query for Week number
Hey Folks, Looking for the simplest of queries to find the week number, given a date. For example, 19th December 2002 falls in the 3rd week, whereas 19th November, 2002, falls in the 4th week. Thanks Raj -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Simple Query for Week number
Dan, Thanks. The W works just fine. What I was looking for.
Phew!!! I almost wrote a PL/SQL for this ;
Fink, Dan
Dan.Fink@mdxTo: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
.comcc:
Sent by: Subject: RE: Simple Query for Week number
root@fatcity.
com
December 19,
2002 12:05 PM
Please
respond to
ORACLE-L
Raj,
How do you define week number? If a month starts in the middle
of
the week, is that week #1 or must it be the first full week?
If the format mask 'W' is adequate, use
1 select sysdate, to_char(sysdate, 'W'),
2 '01-DEC-02', to_char(to_date('01-DEC-02'), 'W'),
3 '30-NOV-02', to_char(to_date('30-NOV-02'), 'W')
4* from dual
SQL /
SYSDATE T '01-DEC-0 T '30-NOV-0 T
- - - - - -
19-DEC-02 3 01-DEC-02 1 30-NOV-02 5
-Original Message-
Sent: Thursday, December 19, 2002 8:54 AM
To: Multiple recipients of list ORACLE-L
Hey Folks,
Looking for the simplest of queries to find the week number, given a date.
For example, 19th December 2002 falls in the 3rd week, whereas 19th
November, 2002, falls in the 4th week.
Thanks
Raj
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Fink, Dan
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Simple Query for Week number
Raj,
How do you define week number? If a month starts in the middle of
the week, is that week #1 or must it be the first full week?
If the format mask 'W' is adequate, use
1 select sysdate, to_char(sysdate, 'W'),
2 '01-DEC-02', to_char(to_date('01-DEC-02'), 'W'),
3 '30-NOV-02', to_char(to_date('30-NOV-02'), 'W')
4* from dual
SQL /
SYSDATE T '01-DEC-0 T '30-NOV-0 T
- - - - - -
19-DEC-02 3 01-DEC-02 1 30-NOV-02 5
-Original Message-
Sent: Thursday, December 19, 2002 8:54 AM
To: Multiple recipients of list ORACLE-L
Hey Folks,
Looking for the simplest of queries to find the week number, given a date.
For example, 19th December 2002 falls in the 3rd week, whereas 19th
November, 2002, falls in the 4th week.
Thanks
Raj
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Fink, Dan
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Simple Query for Week number
select to_char(sysdate, 'W') from dual; Paul -Original Message- Sent: Thursday, December 19, 2002 10:54 AM To: Multiple recipients of list ORACLE-L Hey Folks, Looking for the simplest of queries to find the week number, given a date. For example, 19th December 2002 falls in the 3rd week, whereas 19th November, 2002, falls in the 4th week. Thanks Raj -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Simple Query for Week number
Check out the date 19/11/2002. format 'W' return 3 instead of 4.
I wrote something to do this. It select the week day number of the current
date,
the week day number of the first of the month and the current week.
If week day of the first of the month is higher then the current week day
Then add 1 to the current week.
SELECT CASE WHEN CUR_DAY FIRST_DAY
THEN CUR_WEEK + 1
ELSECUR_WEEK + 0
END CASE
FROM
(SELECT TO_CHAR(TO_DATE('07/12/2002','DD/MM/'),'D') CUR_DAY FROM DUAL),
(SELECT TO_CHAR( TO_DATE('01/' || to_char(last_day(TO_DATE
('07/12/2002','DD/MM/')),'MM/'),'DD/MM/'),'D') FIRST_DAY
from dual) ,
(SELECT TO_CHAR(TO_DATE('07/12/2002','DD/MM/'),'W') CUR_WEEK FROM DUAL)
;
Yechiel Adar
Mehish
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Thursday, December 19, 2002 7:59 PM
Dan, Thanks. The W works just fine. What I was looking for.
Phew!!! I almost wrote a PL/SQL for this ;
Fink, Dan
Dan.Fink@mdxTo: Multiple recipients of
list ORACLE-L [EMAIL PROTECTED]
.comcc:
Sent by: Subject: RE: Simple Query for
Week number
root@fatcity.
com
December 19,
2002 12:05 PM
Please
respond to
ORACLE-L
Raj,
How do you define week number? If a month starts in the middle
of
the week, is that week #1 or must it be the first full week?
If the format mask 'W' is adequate, use
1 select sysdate, to_char(sysdate, 'W'),
2 '01-DEC-02', to_char(to_date('01-DEC-02'), 'W'),
3 '30-NOV-02', to_char(to_date('30-NOV-02'), 'W')
4* from dual
SQL /
SYSDATE T '01-DEC-0 T '30-NOV-0 T
- - - - - -
19-DEC-02 3 01-DEC-02 1 30-NOV-02 5
-Original Message-
Sent: Thursday, December 19, 2002 8:54 AM
To: Multiple recipients of list ORACLE-L
Hey Folks,
Looking for the simplest of queries to find the week number, given a date.
For example, 19th December 2002 falls in the 3rd week, whereas 19th
November, 2002, falls in the 4th week.
Thanks
Raj
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Fink, Dan
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Yechiel Adar
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Simple Query for Week number
Yechiel,
Excellent! It all depends on how 'WEEK' is defined. (Shades of our
former President). Oracle appears to define it as 7 day increments. So,
using November as an example.
Nov 1, 2 - Week 1? or Nov 1 - 7
Nov 3 - 9 - Week 2? or Nov 8 - 14
One of those vexing business questions. My rule of thumb is, whatever you
code, the business side will say is wrong. When you fix the code, the
business side will say that is wrong as well...
Dan
-Original Message-
Sent: Thursday, December 19, 2002 11:53 AM
To: Multiple recipients of list ORACLE-L
Check out the date 19/11/2002. format 'W' return 3 instead of 4.
I wrote something to do this. It select the week day number of the current
date,
the week day number of the first of the month and the current week.
If week day of the first of the month is higher then the current week day
Then add 1 to the current week.
SELECT CASE WHEN CUR_DAY FIRST_DAY
THEN CUR_WEEK + 1
ELSECUR_WEEK + 0
END CASE
FROM
(SELECT TO_CHAR(TO_DATE('07/12/2002','DD/MM/'),'D') CUR_DAY FROM DUAL),
(SELECT TO_CHAR( TO_DATE('01/' || to_char(last_day(TO_DATE
('07/12/2002','DD/MM/')),'MM/'),'DD/MM/'),'D') FIRST_DAY
from dual) ,
(SELECT TO_CHAR(TO_DATE('07/12/2002','DD/MM/'),'W') CUR_WEEK FROM DUAL)
;
Yechiel Adar
Mehish
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Thursday, December 19, 2002 7:59 PM
Dan, Thanks. The W works just fine. What I was looking for.
Phew!!! I almost wrote a PL/SQL for this ;
Fink, Dan
Dan.Fink@mdxTo: Multiple recipients of
list ORACLE-L [EMAIL PROTECTED]
.comcc:
Sent by: Subject: RE: Simple Query for
Week number
root@fatcity.
com
December 19,
2002 12:05 PM
Please
respond to
ORACLE-L
Raj,
How do you define week number? If a month starts in the middle
of
the week, is that week #1 or must it be the first full week?
If the format mask 'W' is adequate, use
1 select sysdate, to_char(sysdate, 'W'),
2 '01-DEC-02', to_char(to_date('01-DEC-02'), 'W'),
3 '30-NOV-02', to_char(to_date('30-NOV-02'), 'W')
4* from dual
SQL /
SYSDATE T '01-DEC-0 T '30-NOV-0 T
- - - - - -
19-DEC-02 3 01-DEC-02 1 30-NOV-02 5
-Original Message-
Sent: Thursday, December 19, 2002 8:54 AM
To: Multiple recipients of list ORACLE-L
Hey Folks,
Looking for the simplest of queries to find the week number, given a date.
For example, 19th December 2002 falls in the 3rd week, whereas 19th
November, 2002, falls in the 4th week.
Thanks
Raj
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Fink, Dan
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Yechiel Adar
INET: [EMAIL PROTECTED
Re: Simple Query for Week number
Thanks Yechiel. I missed that. That is a life saver.
Yechiel
AdarTo: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
adar76@intercc:
.net.il Subject: Re: Simple Query for Week number
Sent by:
root@fatcity.
com
December 19,
2002 01:52 PM
Please
respond to
ORACLE-L
Check out the date 19/11/2002. format 'W' return 3 instead of 4.
I wrote something to do this. It select the week day number of the current
date,
the week day number of the first of the month and the current week.
If week day of the first of the month is higher then the current week day
Then add 1 to the current week.
SELECT CASE WHEN CUR_DAY FIRST_DAY
THEN CUR_WEEK + 1
ELSECUR_WEEK + 0
END CASE
FROM
(SELECT TO_CHAR(TO_DATE('07/12/2002','DD/MM/'),'D') CUR_DAY FROM
DUAL),
(SELECT TO_CHAR( TO_DATE('01/' || to_char(last_day(TO_DATE
('07/12/2002','DD/MM/')),'MM/'),'DD/MM/'),'D')
FIRST_DAY
from dual) ,
(SELECT TO_CHAR(TO_DATE('07/12/2002','DD/MM/'),'W') CUR_WEEK FROM DUAL)
;
Yechiel Adar
Mehish
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Thursday, December 19, 2002 7:59 PM
Dan, Thanks. The W works just fine. What I was looking for.
Phew!!! I almost wrote a PL/SQL for this ;
Fink, Dan
Dan.Fink@mdxTo: Multiple recipients of
list ORACLE-L [EMAIL PROTECTED]
.comcc:
Sent by: Subject: RE: Simple Query
for
Week number
root@fatcity.
com
December 19,
2002 12:05 PM
Please
respond to
ORACLE-L
Raj,
How do you define week number? If a month starts in the middle
of
the week, is that week #1 or must it be the first full week?
If the format mask 'W' is adequate, use
1 select sysdate, to_char(sysdate, 'W'),
2 '01-DEC-02', to_char(to_date('01-DEC-02'), 'W'),
3 '30-NOV-02', to_char(to_date('30-NOV-02'), 'W')
4* from dual
SQL /
SYSDATE T '01-DEC-0 T '30-NOV-0 T
- - - - - -
19-DEC-02 3 01-DEC-02 1 30-NOV-02 5
-Original Message-
Sent: Thursday, December 19, 2002 8:54 AM
To: Multiple recipients of list ORACLE-L
Hey Folks,
Looking for the simplest of queries to find the week number, given a
date.
For example, 19th December 2002 falls in the 3rd week, whereas 19th
November, 2002, falls in the 4th week.
Thanks
Raj
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command
