niXforums Forum Index
 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   PreferencesPreferences   Log in to check your private messagesLog in to check your private messages   Log inLog in 
·  nixdoc.net ·  man pages ·  Linux HOWTOs ·  FreeBSD Tips ·  Forums
navigation Forum index » Databases » Oracle
Weeknumber of a year
Post new topic   Reply to topic Page 1 of 1 [4 Posts] View previous topic :: View next topic
Author Message
Charles Hooper
*nix forums addict


Joined: 09 Jul 2006
Posts: 51

PostPosted: Thu Jul 20, 2006 10:52 am    Post subject: Re: Weeknumber of a year Reply with quote
Sybrand Bakker wrote:
Quote:
On 19 Jul 2006 13:23:32 -0700, purush.venkat@gmail.com wrote:

Hi Experts,

I have a question where in i have to get a weeknumber as follows :

Week Week Ending

1 1/5/2006
2 1/12/2006
3 1/19/2006
4 1/26/2006
5 2/2/2006
6 2/9/2006

etc. so on and so forth.

Can anyone help me with this in ORACLE 9i please?

Thanks,
-- Tom .

Please look up the WW date format model and the IW format model under
'Date format models' in the 'SQL reference manual'

Please avoid asking doc questions, try to be somewhat more industrious
and search on http://tahiti.oracle.com *prior* to posting.


--
Sybrand Bakker, Senior Oracle DBA

Test run comparing WW, IW, and calculated method that I posted, with
the week ending on Thursdays per the original request (note: substitute
any other table with at least 300 rows for the PART table in the query
below):

SELECT
TO_DATE('12/31/2005','MM/DD/YYYY')+ROWNUM CHK_DATE,
TO_CHAR(TO_DATE('12/31/2005','MM/DD/YYYY')+ROWNUM,'WW') WW,
TO_CHAR(TO_DATE('12/31/2005','MM/DD/YYYY')+ROWNUM,'IW') IW,

CEIL(((TO_DATE('12/31/2005','MM/DD/YYYY')+ROWNUM)-TO_DATE('01/05/2006','MM/DD/YYYY'))/7)+1
CALC
FROM
PART
WHERE
ROWNUM<301;

CHK_DATE WW IW CALC
1-Jan-06 01 52 1
2-Jan-06 01 01 1
3-Jan-06 01 01 1
4-Jan-06 01 01 1
5-Jan-06 01 01 1
6-Jan-06 01 01 2
7-Jan-06 01 01 2
8-Jan-06 02 01 2
9-Jan-06 02 02 2
10-Jan-06 02 02 2
11-Jan-06 02 02 2
12-Jan-06 02 02 2
13-Jan-06 02 02 3
14-Jan-06 02 02 3
15-Jan-06 03 02 3
16-Jan-06 03 03 3
17-Jan-06 03 03 3
18-Jan-06 03 03 3
19-Jan-06 03 03 3
20-Jan-06 03 03 4
21-Jan-06 03 03 4
22-Jan-06 04 03 4
23-Jan-06 04 04 4
24-Jan-06 04 04 4
25-Jan-06 04 04 4
26-Jan-06 04 04 4
27-Jan-06 04 04 5
28-Jan-06 04 04 5
29-Jan-06 05 04 5
30-Jan-06 05 05 5
31-Jan-06 05 05 5
1-Feb-06 05 05 5
2-Feb-06 05 05 5
3-Feb-06 05 05 6
4-Feb-06 05 05 6
5-Feb-06 06 05 6
6-Feb-06 06 06 6
7-Feb-06 06 06 6
8-Feb-06 06 06 6
9-Feb-06 06 06 6
10-Feb-06 06 06 7

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.
Back to top
Sybrand Bakker
*nix forums Guru


Joined: 03 Apr 2005
Posts: 1766

PostPosted: Wed Jul 19, 2006 9:07 pm    Post subject: Re: Weeknumber of a year Reply with quote
On 19 Jul 2006 13:23:32 -0700, purush.venkat@gmail.com wrote:

Quote:
Hi Experts,

I have a question where in i have to get a weeknumber as follows :

Week Week Ending

1 1/5/2006
2 1/12/2006
3 1/19/2006
4 1/26/2006
5 2/2/2006
6 2/9/2006

etc. so on and so forth.

Can anyone help me with this in ORACLE 9i please?

Thanks,
-- Tom .

Please look up the WW date format model and the IW format model under
'Date format models' in the 'SQL reference manual'

Please avoid asking doc questions, try to be somewhat more industrious
and search on http://tahiti.oracle.com *prior* to posting.


--
Sybrand Bakker, Senior Oracle DBA
Back to top
Charles Hooper
*nix forums addict


Joined: 09 Jul 2006
Posts: 51

PostPosted: Wed Jul 19, 2006 9:01 pm    Post subject: Re: Weeknumber of a year Reply with quote
purush.venkat@gmail.com wrote:
Quote:
Hi Experts,

I have a question where in i have to get a weeknumber as follows :

Week Week Ending

1 1/5/2006
2 1/12/2006
3 1/19/2006
4 1/26/2006
5 2/2/2006
6 2/9/2006

etc. so on and so forth.

Can anyone help me with this in ORACLE 9i please?

Thanks,
-- Tom .

Starting point - experiment:
Find the number of days, divide by 7, round up, add 1 to the result:
SELECT
CEIL((TRUNC(SYSDATE)-TO_DATE('01/05/2006','MM/DD/YYYY'))/7)+1
FROM
DUAL;

Plugging in a date in place of the current date in the previous
statement:
SELECT

CEIL((TO_DATE('01/04/2006','MM/DD/YYYY')-TO_DATE('01/05/2006','MM/DD/YYYY'))/7)+1
FROM
DUAL;

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.
Back to top
purush.venkat@gmail.com
*nix forums beginner


Joined: 19 Jul 2006
Posts: 1

PostPosted: Wed Jul 19, 2006 8:23 pm    Post subject: Weeknumber of a year Reply with quote
Hi Experts,

I have a question where in i have to get a weeknumber as follows :

Week Week Ending

1 1/5/2006
2 1/12/2006
3 1/19/2006
4 1/26/2006
5 2/2/2006
6 2/9/2006

etc. so on and so forth.

Can anyone help me with this in ORACLE 9i please?

Thanks,
-- Tom .
Back to top
Google
Back to top
Display posts from previous:   
Post new topic   Reply to topic Page 1 of 1 [4 Posts] View previous topic :: View next topic
The time now is Thu Dec 04, 2008 4:31 am | All times are GMT
navigation Forum index » Databases » Oracle
Jump to:  

Similar Topics
Topic Author Forum Replies Last Post
No new posts How to convert current date to last year date? phforum PHP 6 Sun Jul 09, 2006 9:05 am
No new posts FAQ 4.12 How do I find the day or week of the year? PerlFAQ Server Perl 0 Wed Jun 28, 2006 7:03 pm
No new posts ORA-12535: TNS:operation timed out errors for over a year... Scott Server 8 Thu Jun 22, 2006 3:32 am
No new posts FAQ 4.12 How do I find the day or week of the year? PerlFAQ Server Perl 0 Sat Jun 17, 2006 1:03 am
No new posts FAQ 4.18 Does Perl have a Year 2000 problem? Is Perl Y2K ... PerlFAQ Server Perl 0 Thu Jun 15, 2006 7:03 pm

MPAA | Loans | Credit Cards | Credit Card | Loans
Copyright © 2004-2005 DeniX Solutions SRL
 
Other DeniX Solutions sites: Unix/Linux blog |  electronics forum |  medicine forum |  science forum | 
Privacy Policy


Powered by phpBB © 2001, 2005 phpBB Group

[ Time: 0.1641s ][ Queries: 20 (0.0817s) ][ GZIP on - Debug on ]