Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Jeni.

Asked: January 23, 2003 - 8:47 pm UTC

Last updated: July 16, 2013 - 3:30 pm UTC

Version: 8.1.7

Viewed 50K+ times! This question is

You Asked

Tom,

Is there a way by using SQL Plus to get a date range that would show the number of week as well?

For example:
January 2003
S M Tu W Th F S
1 2 3 4
5 6 7 8 9 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28 29 30 31

I would like to show 1/1/03 - 1/4/03 as "week 1" of Jan. 03
1/5/03 - 1/11/03 as "week 2" of Jan. 03
1/12/03 - 1/18/03 as "week 3" Jan. 03 etc..

Is there a way I can do this?

Thanks for your help!

and Tom said...

I think IW with a slight "shift" will do it:

try: to_char( the_date+1, 'iw' )

eg:

ops$tkyte@ORA920> select dt, to_char( dt+1, 'iw' )
2 from ( select to_date('20-dec-2002')+rownum dt
3 from all_objects
4 where rownum < 45 );

DT TO
--------- --
21-DEC-02 51
22-DEC-02 52
23-DEC-02 52
24-DEC-02 52
25-DEC-02 52
26-DEC-02 52
27-DEC-02 52
28-DEC-02 52
29-DEC-02 01
30-DEC-02 01
31-DEC-02 01
01-JAN-03 01
02-JAN-03 01
03-JAN-03 01
04-JAN-03 01
05-JAN-03 02
06-JAN-03 02
07-JAN-03 02
08-JAN-03 02
09-JAN-03 02
10-JAN-03 02
11-JAN-03 02
12-JAN-03 03
13-JAN-03 03
14-JAN-03 03
15-JAN-03 03
16-JAN-03 03
17-JAN-03 03
18-JAN-03 03
19-JAN-03 04
20-JAN-03 04
21-JAN-03 04
...

Rating

  (46 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Excellent...............

Sikandar Hayat Awan, January 24, 2003 - 1:28 am UTC

I was searching for the same option and you gave the solution. Thanks

Secondly I have purchased your book and now I am reading it, and found it to be very informative.

Can you explain your query...

Jeni, January 24, 2003 - 2:37 am UTC

Tom,

I have a few more question from your query.

1. What is "IW"?
2. What do you mean by slight "shift"?



Tom Kyte
January 24, 2003 - 7:17 am UTC

IW = iso week = documented date format
</code> http://docs.oracle.com/docs/cd/B10501_01/server.920/a96540/sql_elements4a.htm#48635 <code>

by shift -- I mean, add a day. the ISO week ends on Sunday, you wanted to end on Saturday. So, we shift the date by a day

Wonderful

Pichaimani Bala, March 28, 2003 - 2:31 pm UTC

Ask Tom is my ready reckoner

How to convert week # back to a date?

A reader, August 20, 2003 - 12:20 pm UTC

How to get the the date of first day of the week for a given week #? For example, the beginning day for week# 34 in 2003 is AUG-18-2003, how do I find the beginning date for the same week # 34 in 2002? 'iw' can't be used in the to_date function.

Thanks!

Tom Kyte
August 21, 2003 - 4:55 pm UTC

well, we can use the fact that at least from 1800 to 2199, jan 4th is in the first week... this:

ops$tkyte@ORA920> select *
  2    from (
  3  select dt1, to_char( dt1, 'yyyy' ),
  4         to_char( dt4, 'iw' ),
  5         decode( to_char( dt4, 'iw' ), '01', null, '******' ) xxx
  6    from (
  7  select add_months( to_date( '01-jan-1800', 'dd-mon-yyyy'), (rownum-1)*12 ) dt1,
  8         add_months( to_date( '02-jan-1800', 'dd-mon-yyyy'), (rownum-1)*12 ) dt2,
  9         add_months( to_date( '03-jan-1800', 'dd-mon-yyyy'), (rownum-1)*12 ) dt3,
 10         add_months( to_date( '04-jan-1800', 'dd-mon-yyyy'), (rownum-1)*12 ) dt4,
 11         add_months( to_date( '05-jan-1800', 'dd-mon-yyyy'), (rownum-1)*12 ) dt5,
 12         add_months( to_date( '06-jan-1800', 'dd-mon-yyyy'), (rownum-1)*12 ) dt6,
 13         add_months( to_date( '07-jan-1800', 'dd-mon-yyyy'), (rownum-1)*12 ) dt7
 14    from all_objects
 15   where rownum <= 400
 16         )
 17         )
 18   where xxx is not null
 19  /

no rows selected


shows that.

Then, we can use the fact that next_day(dt-7,'mon') returns the monday of that week that DT falls in.

sooo, it would seem that if we

take jan-4th of the year of interest.

we add to that the (week-2)* 7 days  (-1 becuase we start at week one, -1 again because we back off 7 days) .

and then ask for the next monday -- we get it:

ops$tkyte@ORA920> variable year varchar2(4)
ops$tkyte@ORA920> variable week number
ops$tkyte@ORA920>
ops$tkyte@ORA920> exec :year := '2003'; :week := 34

PL/SQL procedure successfully completed.

ops$tkyte@ORA920>
ops$tkyte@ORA920> select next_day( to_date( '04-jan-' || :year, 'dd-mon-yyyy' ) + (:week-2)*7, 'mon' )
  2    from dual
  3  /

NEXT_DAY(TO_DATE('04
--------------------
18-aug-2003 00:00:00



this query generates all of the "first days of the week" by IW week for a year:

ops$tkyte@ORA920> select year, week,
  2         next_day( to_date( '04-jan-' || year, 'dd-mon-yyyy' ) + (week-2)*7, 'mon' )
  3    from (select '2003' year, rownum week from all_objects where rownum <= 53 )
  4  /

YEAR       WEEK NEXT_DAY(TO_DATE('04
---- ---------- --------------------
2003          1 30-dec-2002 00:00:00
2003          2 06-jan-2003 00:00:00
2003          3 13-jan-2003 00:00:00
2003          4 20-jan-2003 00:00:00
2003          5 27-jan-2003 00:00:00
2003          6 03-feb-2003 00:00:00
2003          7 10-feb-2003 00:00:00
2003          8 17-feb-2003 00:00:00
2003          9 24-feb-2003 00:00:00
2003         10 03-mar-2003 00:00:00
2003         11 10-mar-2003 00:00:00
2003         12 17-mar-2003 00:00:00
2003         13 24-mar-2003 00:00:00
2003         14 31-mar-2003 00:00:00
2003         15 07-apr-2003 00:00:00
2003         16 14-apr-2003 00:00:00
2003         17 21-apr-2003 00:00:00
2003         18 28-apr-2003 00:00:00
2003         19 05-may-2003 00:00:00
2003         20 12-may-2003 00:00:00
2003         21 19-may-2003 00:00:00
2003         22 26-may-2003 00:00:00
2003         23 02-jun-2003 00:00:00
2003         24 09-jun-2003 00:00:00
2003         25 16-jun-2003 00:00:00
2003         26 23-jun-2003 00:00:00
2003         27 30-jun-2003 00:00:00
2003         28 07-jul-2003 00:00:00
2003         29 14-jul-2003 00:00:00
2003         30 21-jul-2003 00:00:00
2003         31 28-jul-2003 00:00:00
2003         32 04-aug-2003 00:00:00
2003         33 11-aug-2003 00:00:00
2003         34 18-aug-2003 00:00:00
2003         35 25-aug-2003 00:00:00
2003         36 01-sep-2003 00:00:00
2003         37 08-sep-2003 00:00:00
2003         38 15-sep-2003 00:00:00
2003         39 22-sep-2003 00:00:00
2003         40 29-sep-2003 00:00:00
2003         41 06-oct-2003 00:00:00
2003         42 13-oct-2003 00:00:00
2003         43 20-oct-2003 00:00:00
2003         44 27-oct-2003 00:00:00
2003         45 03-nov-2003 00:00:00
2003         46 10-nov-2003 00:00:00
2003         47 17-nov-2003 00:00:00
2003         48 24-nov-2003 00:00:00
2003         49 01-dec-2003 00:00:00
2003         50 08-dec-2003 00:00:00
2003         51 15-dec-2003 00:00:00
2003         52 22-dec-2003 00:00:00
2003         53 29-dec-2003 00:00:00

53 rows selected.



please -- check out the boundary value conditions!!! (eg: test this, date things can be tricky) 

It works!

A reader, August 22, 2003 - 11:38 am UTC

Thanks Tom!

You say "...check out the boundary value conditions!!! (eg: test this, date things can be tricky)"

Are saying check for year 1800 and 2199 or did you mean something else?


Tom Kyte
August 22, 2003 - 7:38 pm UTC

first day/last day or months -- leap years anything on boundaries.

I'm pretty sure the logic is "sound", you need to verify it (and understand it)

How can I get the month via iso week?

David Pujol, September 05, 2003 - 1:05 pm UTC

I'm building a time table. I'm computing the weeks with standard iso ('IW') and weeks with 'WW'. For example:

/SYSTEM@BBR.ORA9R2> select to_char(to_date('30-09-2003', 'dd-mm-yyyy'), 'WW') from dual;

TO
--
39

/SYSTEM@BBR.ORA9R2> select to_char(to_date('30-09-2003', 'dd-mm-yyyy'), 'IW') from dual;

TO
--
40

When I analyze the data group by week I can choose with Iso or normal option, but when I analyze the same data group by month (for example), it can be possible that month be 9 or 10 (depends of iso week). How can I compute month, quarter, .. from standard week iso?. I know that there is iso year but I don't find a iso for month, quarter, .. for example.

example of my table:
numweek,
numweekiso,
nummonth,
nummonthiso,
namemonth,
namemonthiso,
numtri,
numtriiso,
numquarter,
numquarteriso ......



Tom Kyte
September 05, 2003 - 6:54 pm UTC

you cannot -- you already said "it might be month 9 or 10". weeks span months, they span qtrs.


there isn't an "iso" month. there are just months 1..12

same with qtr -- qtrs 1..4

perhaps you are just looking for "mm" and "q" formats?

EXCELLENT!!!!!!!!!

Reader, September 05, 2003 - 10:16 pm UTC


How can I implement this "iso month" from iso week?

David Pujol, September 07, 2003 - 4:06 am UTC

I'm computing months and quarters. Let me show you my problem with one example:

I'm grounping by "iso" week and there is one date (31-12-2003, for example) that iso week is 01, so if iso week is 1, then I need that month be 1, quarter be 1 and year (iso year) be 2004.
I have too, another version for this date (normal version) that group by standart week (week 53, month 12, quarter 3 and year 2003)

<ORA817@SYSTEM> select to_char(to_date('31-12-2003', 'dd-mm-yyyy'), 'IW') from dual;

TO
--
01

<ORA817@SYSTEM> select to_char(to_date('31-12-2003', 'dd-mm-yyyy'), 'ww') from dual;

TO
--
53

I don't know if you understand me (sorry for my english) but I need that month, quarter and year be consistent with week (iso and standart) result, so, if week is 01, I need that month became 1 and ...,

I have a customer that wants analyze his data with iso week, and he wants that when he groups by month, the result be coherent with the iso Week. In my example, the 31-12-2003 will not be computed in 2003 year if we've grouped by iso week and then we've grouped by month.

How can I implement this "iso month" from iso week??

A lot of thanks!!!!

Tom Kyte
September 07, 2003 - 6:13 pm UTC

guess you can use decode and say "hey, if iso week = 1, then qtr = 1, month = 1 else qtr = to_char and month = to_char"


you know, just treat iso week = 1 differently using case or decode.

weeks within the month

Muhammad Ahmad Imran, September 08, 2003 - 9:50 pm UTC

Tom, your results are on the basis of year. How can I get the results on the basis of month as 08-sep-2003 is the week 2.



Tom Kyte
September 09, 2003 - 11:25 am UTC

not sure what you are asking for here.

Explaination the prev. comment

Muhammad Ahmad Imran, September 09, 2003 - 10:08 pm UTC

Tom Let's say I have the monthS as:

January 2003 February 2003
S M Tu W Th F S S M T W T F S
1 2 3 4 1
5 6 7 8 9 10 11 2 3 4 5 6 7 8
12 13 14 15 16 17 18 9 10 11 12 13 14 15
19 20 21 22 23 24 25 16 17 18 19 20 21 22
26 27 28 29 30 31 23 24 25 26 27 28

I am trying for a function which accept a date argument and returns a number which show the week of the month.

Let I say 12/1/2003 seeing above calendar function should return 1 and so on...
e.g.

Date Week of Month
3/1/03 1
4/1/03 1
5/1/03 2
21/1/03 4
1/2/03 1
28/2/03 5 and so on....

I have a database of a college and in which I have a table which have the enquires about certain courses through out the month now i want to generate a report which show in which days of any month(start,middle,last) there are the most enquires.

I think now you understand my problem.

Tom Kyte
September 10, 2003 - 10:46 am UTC

just a little math....


ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> create table t (x date );

Table created.

ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> insert into t select to_date('01-jan-2003')+rownum-1
  2   from all_objects
  3  where rownum <= 60;

60 rows created.

ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> select x,
  2         next_day(trunc(x,'mm')-8, 'sun'),
  3         trunc( (x-next_day(trunc(x,'mm')-8, 'sun'))/7 )+1 "week"
  4   from t;

X         NEXT_DAY(       week
--------- --------- ----------
01-JAN-03 29-DEC-02          1
02-JAN-03 29-DEC-02          1
03-JAN-03 29-DEC-02          1
04-JAN-03 29-DEC-02          1
05-JAN-03 29-DEC-02          2
06-JAN-03 29-DEC-02          2
07-JAN-03 29-DEC-02          2
08-JAN-03 29-DEC-02          2
09-JAN-03 29-DEC-02          2
10-JAN-03 29-DEC-02          2
11-JAN-03 29-DEC-02          2
12-JAN-03 29-DEC-02          3
13-JAN-03 29-DEC-02          3
14-JAN-03 29-DEC-02          3
15-JAN-03 29-DEC-02          3
16-JAN-03 29-DEC-02          3
17-JAN-03 29-DEC-02          3
18-JAN-03 29-DEC-02          3
19-JAN-03 29-DEC-02          4
20-JAN-03 29-DEC-02          4
21-JAN-03 29-DEC-02          4
22-JAN-03 29-DEC-02          4
23-JAN-03 29-DEC-02          4
24-JAN-03 29-DEC-02          4
25-JAN-03 29-DEC-02          4
26-JAN-03 29-DEC-02          5
27-JAN-03 29-DEC-02          5
28-JAN-03 29-DEC-02          5
29-JAN-03 29-DEC-02          5
30-JAN-03 29-DEC-02          5
31-JAN-03 29-DEC-02          5
01-FEB-03 26-JAN-03          1
02-FEB-03 26-JAN-03          2
03-FEB-03 26-JAN-03          2
04-FEB-03 26-JAN-03          2
05-FEB-03 26-JAN-03          2
06-FEB-03 26-JAN-03          2
07-FEB-03 26-JAN-03          2
08-FEB-03 26-JAN-03          2
09-FEB-03 26-JAN-03          3
10-FEB-03 26-JAN-03          3
11-FEB-03 26-JAN-03          3
12-FEB-03 26-JAN-03          3
13-FEB-03 26-JAN-03          3
14-FEB-03 26-JAN-03          3
15-FEB-03 26-JAN-03          3
16-FEB-03 26-JAN-03          4
17-FEB-03 26-JAN-03          4
18-FEB-03 26-JAN-03          4
19-FEB-03 26-JAN-03          4
20-FEB-03 26-JAN-03          4
21-FEB-03 26-JAN-03          4
22-FEB-03 26-JAN-03          4
23-FEB-03 26-JAN-03          5
24-FEB-03 26-JAN-03          5
25-FEB-03 26-JAN-03          5
26-FEB-03 26-JAN-03          5
27-FEB-03 26-JAN-03          5
28-FEB-03 26-JAN-03          5
01-MAR-03 23-FEB-03          1

60 rows selected.

 

week of month

Howard, October 25, 2003 - 5:13 pm UTC

Hi Tom,
when I tried the code below (got your formula)


select x,
next_day(trunc(x,'mm')-8, 'sun'),
trunc( (x-next_day(trunc(x,'mm')-8, 'sun'))/7 )+1 "week"
from
(select to_date('01-jan-2003')+rownum-1 x
from all_objects
where rownum <= 260
)



I found that the month of Jun, 2003 does not have week 1.

X NEXT_DAY( week
--------- --------- ----------
........
........
24-MAY-03 27-APR-03 4
25-MAY-03 27-APR-03 5
26-MAY-03 27-APR-03 5
27-MAY-03 27-APR-03 5
28-MAY-03 27-APR-03 5
29-MAY-03 27-APR-03 5
30-MAY-03 27-APR-03 5
31-MAY-03 27-APR-03 5
01-JUN-03 25-MAY-03 2 <<<<-------- should be 1
02-JUN-03 25-MAY-03 2
03-JUN-03 25-MAY-03 2


seems there is a bug in the formula.


Tom Kyte
October 26, 2003 - 9:21 am UTC

yup, you'll need to add a decode in there to check for months that actually begin on sundays (boundary value condition i warned about looking for :)

select x,
next_day(trunc(x,'mm')-8, 'sun'),
decode( trunc(x), trunc(x,'mm'), 1, trunc( (x-next_day(trunc(x,'mm')-8, 'sun'))/7 )+1 ) "week",

decode( decode( trunc(x), trunc(x,'mm'), 1, trunc( (x-next_day(trunc(x,'mm')-8, 'sun'))/7 )+1 ),
trunc( (x-next_day(trunc(x,'mm')-8, 'sun'))/7 )+1, null, '<<<<<' )
from (select to_date('01-jan-2003')+rownum-1 x
from all_objects
where rownum <= 400
)
/


should do it -- it flags the two rows (jun1 2003 and feb1 2004) in that result set that would be affected. we'll just return 1 on the first day of the month, do the math otherwise.

week of month

Howard, October 26, 2003 - 12:09 pm UTC

Tom, you are simply the best!!!!!!!

thanks.


week of month

A reader, October 27, 2003 - 12:08 pm UTC

Hi Tom,
Sorry keeps bugging you at this topic.....


select x,
next_day(trunc(x,'mm')-8, 'sun'),
decode( trunc(x), trunc(x,'mm'), 1, trunc( (x-next_day(trunc(x,'mm')-8,
'sun'))/7 )+1 ) "week",
decode( decode( trunc(x), trunc(x,'mm'), 1, trunc(
(x-next_day(trunc(x,'mm')-8, 'sun'))/7 )+1 ),
trunc( (x-next_day(trunc(x,'mm')-8, 'sun'))/7 )+1, null, '<<<<<'
)
from (select to_date('01-jan-2003')+rownum-1 x
from all_objects
where rownum <= 400
)
/

X NEXT_DAY( week DECOD
--------- --------- ---------- -----
30-MAY-03 27-APR-03 5
31-MAY-03 27-APR-03 5
01-JUN-03 25-MAY-03 1 <<<<<
02-JUN-03 25-MAY-03 2 <<<<------- should be 1
03-JUN-03 25-MAY-03 2 <<<<------- should be 1
04-JUN-03 25-MAY-03 2 <<<<------- should be 1
05-JUN-03 25-MAY-03 2 <<<<------- should be 1
06-JUN-03 25-MAY-03 2 <<<<------- should be 1
07-JUN-03 25-MAY-03 2 <<<<------- should be 1
08-JUN-03 25-MAY-03 3 <<<<------- should be 2
09-JUN-03 25-MAY-03 3 <<<<------- should be 2
10-JUN-03 25-MAY-03 3 <<<<------- should be 2
11-JUN-03 25-MAY-03 3 <<<<------- should be 2
12-JUN-03 25-MAY-03 3 <<<<------- should be 2
13-JUN-03 25-MAY-03 3 <<<<------- should be 2
14-JUN-03 25-MAY-03 3 <<<<------- should be 2
15-JUN-03 25-MAY-03 4 <<<<------- should be 3
16-JUN-03 25-MAY-03 4 <<<<------- should be 3
17-JUN-03 25-MAY-03 4 <<<<------- should be 3
18-JUN-03 25-MAY-03 4 <<<<------- should be 3
19-JUN-03 25-MAY-03 4 <<<<------- should be 3
20-JUN-03 25-MAY-03 4 <<<<------- should be 3
21-JUN-03 25-MAY-03 4 <<<<------- should be 3
22-JUN-03 25-MAY-03 5 <<<<------- should be 4
23-JUN-03 25-MAY-03 5 <<<<------- should be 4
24-JUN-03 25-MAY-03 5 <<<<------- should be 4
25-JUN-03 25-MAY-03 5 <<<<------- should be 4
26-JUN-03 25-MAY-03 5 <<<<------- should be 4
27-JUN-03 25-MAY-03 5 <<<<------- should be 4
28-JUN-03 25-MAY-03 5 <<<<------- should be 4
29-JUN-03 25-MAY-03 6 <<<<------- should be 5
30-JUN-03 25-MAY-03 6 <<<<------- should be 5
01-JUL-03 29-JUN-03 1
02-JUL-03 29-JUN-03 1
03-JUL-03 29-JUN-03 1
04-JUL-03 29-JUN-03 1
05-JUL-03 29-JUN-03 1
06-JUL-03 29-JUN-03 2



I tried the query below and seems give the expected results........


select x dt,
(to_char( x+1, 'iw') - to_char(to_date('01/'||to_char(x,'mon/')||to_char(x,'yyyy'),'dd/mon/yyyy') + 1,'iw') + 1)
Week_of_month
from
(select to_date('01-jan-2003','dd/mon/yyyy')+rownum-1 x
from all_objects
where rownum <= 400
)
/


Thanks.

Week range

siraj, January 14, 2004 - 2:21 am UTC

select dt, to_char( dt+1, 'iw' )
from ( select to_date('20-dec-2002')+rownum dt
from all_objects
where rownum < 45 );
I try to use the above command but result shows
29-DEC-02 01
30-DEC-02 01
31-DEC-02 01
01-JAN-03 01

31-dec-02 is show 01 week and 01-jan-03 also show 01 week
but need 31-dec-02 shows 53, bec my client wants 01-jan-2002 is 1st week and 31 dec is lask week . If 01-jan-2003 is next year so he wants 1st week start from 1st day and last week end to dec 31

Tom Kyte
January 14, 2004 - 3:31 pm UTC

iw is defined outside of oracle here -- it is "a standard" -- the i = iso.

This is my bible

Rani Nemani, June 04, 2004 - 10:57 am UTC

Thanks

Date range per week

Winfried Hegener, July 23, 2004 - 7:46 am UTC

Very helpful, Thanks

Very good !

Vincent Heintz, October 19, 2004 - 5:07 am UTC

Thanks Tom ! Your solution is much more brilliant than mine. My very little advantage is that my solution don't need conditions like "4/1 is in first iso week between 1800 and 2199" but it's a poor argument !
Well, just this :

-- idate is YYYYWW where WW is iso week
CREATE OR REPLACE FUNCTION todate_ww (idate IN VARCHAR2) RETURN DATE IS
yyyy VARCHAR2(4);
ww VARCHAR2(2);
JanFirst DATE;
RankFirstMonday INTEGER;
RankMonFirstWeek INTEGER;
RankMondayMyWeek INTEGER;
BEGIN
-- Splitting into fields
yyyy := SubStr(idate, 1, 4);
ww := SubStr(idate, 5, 2);
JanFirst := To_Date('01/01/'||yyyy, 'DD/MM/YYYY');
-- Rank of the first monday of the year
RankFirstMonday :=
(9 - To_Number(To_Char(JanFirst, 'D'))) MOD 7;
IF (RankFirstMonday = 0) THEN
RankFirstMonday := 7;
END IF;
IF (RankFirstMonday >= 5) THEN
RankMonFirstWeek := RankFirstMonday-8;
ELSE
RankMonFirstWeek := RankFirstMonday;
END IF;
RankMondayMyWeek := RankMonFirstWeek+(To_Number(ww)-1)*7;
IF (RankMonFirstWeek > 0) THEN
RankMondayMyWeek := RankMondayMyWeek -1;
END IF;
RETURN(JanFirst+RankMondayMyWeek);
END;

Thanks Tom,
Vincent

Excuse me !

Vincent Heintz, October 19, 2004 - 6:24 am UTC

well, some more neurons on the problem let me understand that your remark on the "limits" of your algorithm refers to the date system of Oracle, not on the definition of the ISO week. It is an evidence (with some minutes thinking on it...) that 4-jan is always on ISO week 1. It is a direct consequence of ISO week definition as precised by ISO itself "Week 01 of a year is per definition the first week that has the Thursday in this year, which is equivalent to the week that contains the fourth day of January".

OK. Thanks,
Vincent

(the end)

Vincent Heintz, October 19, 2004 - 6:28 am UTC

... and I will throw away my heavy PL/SQL code quoted above as its existence has *NO* more justification.
Bye,
Vincent

Week number

vivek, April 22, 2005 - 9:00 am UTC

Our client want to have week number for the date supplied and week should start from saturday. We got the result by using iw-5 in date. I used the following query to get result, which was acceptable by client

select dt, to_char( dt, 'ww') ww, to_char( dt-5, 'iw') iw
from ( select to_date('21-dec-2004')+rownum dt
from all_objects
where rownum < 30))

But additionally what he want now is , if the first day of the year is saturday then week number should start from there. As 01-JAN-05 was saturday it should start from first week of this year. Is there a way we can get this result. Data set we are working on is for '2000- 2007' year.

Regards

Tom Kyte
April 22, 2005 - 10:43 am UTC

don't know what you mean.

week number

Vivek, April 25, 2005 - 4:45 am UTC

Our client want to have week number for the date and week should start from Saturday. and if week continued into new year then it should show the week number of last year, eg

DATE ww week number iw week number

24-DEC-03 52 51
25-DEC-03 52 51
26-DEC-03 52 51
27-DEC-03 52 52
28-DEC-03 52 52
29-DEC-03 52 52
30-DEC-03 52 52
31-DEC-03 53 52
01-JAN-04 01 52
02-JAN-04 01 52
03-JAN-04 01 01
04-JAN-04 01 01
05-JAN-04 01 01
06-JAN-04 01 01
07-JAN-04 01 01
08-JAN-04 02 01
09-JAN-04 02 01
10-JAN-04 02 02
11-JAN-04 02 02

What he wants now that if the first day of year is saturday, the week number one for new year should start from there , like 01-JAN-05 is saturday, so it should be week number one, irrespective that it is continued from last year.
DATE ww week number iw week number

25-DEC-04 52 52
26-DEC-04 52 52
27-DEC-04 52 52
28-DEC-04 52 52
29-DEC-04 52 52
30-DEC-04 53 52
31-DEC-04 53 52
01-JAN-05 01 53 <<== 01
02-JAN-05 01 53 <<== 01
03-JAN-05 01 53
04-JAN-05 01 53
05-JAN-05 01 53
06-JAN-05 01 53
07-JAN-05 01 53
08-JAN-05 02 01 <<==02
09-JAN-05 02 01
10-JAN-05 02 01
11-JAN-05 02 01

I tried to do this with
select dt,decode(substr(to_char( to_date(trunc(dt,'YY')), 'DAY'),1,8),'SATURDAY',ww,iw) DAY
from (select dt, to_char( dt, 'ww') ww, to_char( dt-5, 'iw') iw
from ( select to_date('21-dec-2004')+rownum dt
from all_objects
where rownum < 30))
/

This SQL gives me trouble when it comes to end of the year. Please advice. Hope i made clear this time.

Thanks


Tom Kyte
April 25, 2005 - 7:31 am UTC

so, sounds like

case when ( to_char(trunc(dt,'y'),'DY') = 'SAT') /* use the right day string..*/
then ww week
else iw week
end


right?


Data ranges

Richard, April 25, 2005 - 8:38 am UTC

Hello Tom,

I am looking for an query which calculates in an efficient manner
consecutive date ranges out of a list of dates.

drop table t;

create table t (d date);

insert into t values(to_date('05.02.2005', 'dd.mm.yyyy');
insert into t values(to_date('06.02.2005', 'dd.mm.yyyy');
insert into t values(to_date('16.02.2005', 'dd.mm.yyyy');
insert into t values(to_date('17.02.2005', 'dd.mm.yyyy');
insert into t values(to_date('18.02.2005', 'dd.mm.yyyy');
insert into t values(to_date('19.02.2005', 'dd.mm.yyyy');
insert into t values(to_date('11.03.2005', 'dd.mm.yyyy');
insert into t values(to_date('16.03.2005', 'dd.mm.yyyy');
insert into t values(to_date('17.03.2005', 'dd.mm.yyyy');
insert into t values(to_date('18.03.2005', 'dd.mm.yyyy');
insert into t values(to_date('19.03.2005', 'dd.mm.yyyy');

This is the result I expect:

FRM UTL
---------- ----------
05.02.2005 06.02.2005
16.02.2005 19.02.2005
11.03.2005 11.03.2005
16.03.2005 19.03.2005

My solution is:

select
*
from
(
select
x.f frm
, nvl(x.u, lead(x.u) over (order by x.d)) utl
from (
select
d
, case when d - lag(d) over (order by d) = 1 then null else d end f
, case when d - lead(d) over (order by d) = -1 then null else d end u
from
t
) x
where 1 = 1
and (f is not null or u is not null)
)
where 1 = 1
and frm is not null
;

The query works as expected but I am curious if there is
more elegant way to solve the problem.

Yours,

Richard


Tom Kyte
April 25, 2005 - 8:56 am UTC

database version is?

week number

Vivek, April 25, 2005 - 9:10 am UTC

Tom,

Your query gives the same result as mine. Problem i mentioned that comes at the end of year is as follows

-----------------------------

1 select dt,case when ( to_char(trunc(dt,'y'),'DY') = 'SAT') /* use the right day string..*/
2 then ww
3 else iw
4 end
5 from (select dt, to_char( dt, 'ww') ww, to_char( dt-5, 'iw') iw
6 from ( select to_date('21-dec-2005')+rownum dt
7 from all_objects
8* where rownum < 30))
pargus@SUNNET> /

DT CASEWHEN(TO_CHAR(TRUNC(DT,'Y')
--------- ---------------------------------------
22-DEC-05 51
23-DEC-05 51
24-DEC-05 52
25-DEC-05 52
26-DEC-05 52
27-DEC-05 52
28-DEC-05 52
29-DEC-05 52
30-DEC-05 52
31-DEC-05 53 <<== ww week is 53
01-JAN-06 52 <<== iw week is 52 ( i want it to be 53 when
02-JAN-06 52 1st day is not saturday)
03-JAN-06 52
04-JAN-06 52
05-JAN-06 52
06-JAN-06 52
07-JAN-06 01
08-JAN-06 01
09-JAN-06 01
10-JAN-06 01
11-JAN-06 01
12-JAN-06 01
13-JAN-06 01
14-JAN-06 02
15-JAN-06 02
------------------------------------------

Thanks


Tom Kyte
April 25, 2005 - 9:21 am UTC

more cases -- you just have exceptions, account for them all and return whatever you want.

You do not want any of the industry standard return values -- list all of your exceptions, look for them and return your value.

(eg: use a bigger CASE statement with more conditions to catch whatever you like. suggest you hide that in a VIEW if possible)


suggestion:

on a piece of paper, write down the conclusive "if then else" processing you would do in procedural code.

Then, write the CASE statement that does it.

Date range

Richard, April 25, 2005 - 9:59 am UTC

Sorry Tom!
I am using 10.1.0.3.0 running on RedHat 3.

Here the problem once again:

I am looking for an query which calculates in an efficient manner
consecutive date ranges out of a list of dates.

drop table t;

create table t (d date);

insert into t values(to_date('05.02.2005', 'dd.mm.yyyy');
insert into t values(to_date('06.02.2005', 'dd.mm.yyyy');
insert into t values(to_date('16.02.2005', 'dd.mm.yyyy');
insert into t values(to_date('17.02.2005', 'dd.mm.yyyy');
insert into t values(to_date('18.02.2005', 'dd.mm.yyyy');
insert into t values(to_date('19.02.2005', 'dd.mm.yyyy');
insert into t values(to_date('11.03.2005', 'dd.mm.yyyy');
insert into t values(to_date('16.03.2005', 'dd.mm.yyyy');
insert into t values(to_date('17.03.2005', 'dd.mm.yyyy');
insert into t values(to_date('18.03.2005', 'dd.mm.yyyy');
insert into t values(to_date('19.03.2005', 'dd.mm.yyyy');

This is the result I expect:

FRM UTL
---------- ----------
05.02.2005 06.02.2005
16.02.2005 19.02.2005
11.03.2005 11.03.2005
16.03.2005 19.03.2005

My solution is:

select
*
from
(
select
x.f frm
, nvl(x.u, lead(x.u) over (order by x.d)) utl
from (
select
d
, case when d - lag(d) over (order by d) = 1 then null else d end f
, case when d - lead(d) over (order by d) = -1 then null else d end u
from
t
) x
where 1 = 1
and (f is not null or u is not null)
)
where 1 = 1
and frm is not null
;

The query works as expected but I am curious if there is
more elegant way to solve the problem.

Yours,

Richard


Tom Kyte
April 25, 2005 - 10:08 am UTC

ops$tkyte@ORA10G> select min(d), max(d)
  2    from (
  3  select d, last_value(grp ignore nulls) over (order by d) grp
  4    from (
  5  select d,
  6         case when lag(d) over (order by d) <> d-1
  7              then row_number() over (order by d)
  8          end grp
  9    from t
 10         )
 11             )
 12   group by grp
 13   order by 1;
 
MIN(D)     MAX(D)
---------- ----------
05.02.2005 06.02.2005
16.02.2005 19.02.2005
11.03.2005 11.03.2005
16.03.2005 19.03.2005


I "tag" the beginning of a new group (row_number)

I "carry it down" (last_value with ignore nulls)

then group.

similar to this:
https://www.oracle.com/technetwork/issue-archive/2014/14-mar/o24asktom-2147206.html
Analytics to the Rescue

but that shows how to do that in 9i with one extra step to mimic the last-value ignore nulls 10g trick. 

Need to find the Week of the Month based on a given date and start DAY of the week

Jagadesh, September 02, 2005 - 5:10 pm UTC

Hello Tom

I need to find out the Week of the month using a Function. The Function has 2 IN Parameters one a GIVEN DATE and the next is the STARTING of the WEEK. That is for a given date it should determine under which week that falls under. The week beginning is given 1 to 7 (1=Sunday ... 7=Saturday) and the week could begin on any one of the 7 days. The output has to be a number 1 to 6. Below example for the month of May 2005.

S M T W T F S
1 2 3 4 5 6 7
8 9 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29 30 31

Week begin is 3 (Tuesday)

I pass in a Date of '05/26/2005' the result should be 5
I pass in a Date of '05/01/2005' the result should be 1
I pass in a Date of '05/02/2005' the result should be 1
I pass in a Date of '05/03/2005' the result should be 2
I pass in a Date of '05/09/2005' the result should be 2
I pass in a Date of '05/10/2005' the result should be 3
I pass in a Date of '05/31/2005' the result should be 6

I need your help on this Function. And we are using Oracle 10G.


Tom Kyte
September 03, 2005 - 8:48 am UTC

test this:

variable dt varchar2(30);
exec :dy := 'THUR'

select the_date,
next_day( last_day(add_months(the_date,-1)), :dy) dy,
to_char( the_date, 'dd' ) x,
to_char(next_day(last_day(add_months(the_date,-1)),:dy),'dd') y,
((to_char( the_date, 'dd' )-to_char(next_day(last_day(add_months(the_date,-1)),:dy),'dd'))/7) wk1,
ceil(0.01+(to_char( the_date, 'dd' )-to_char(next_day(last_day(add_months(the_date,-1)),:dy),'dd'))/7) wk

from (select to_date( '06/01/2005','mm/dd/yyyy')+level-1 the_date
from dual
connect by level <= 31
)
/


add one to wk.

Jagadesh, September 06, 2005 - 2:11 pm UTC

Hi Tom

There is a flaw in the Query, as you said if I add 1 to the wk, it is good for the current month and not good for the August. It is because the Week beginning is Monday and the Month beginning is also Monday and if I add 1 to the wk the weeks is wrong. Can you please rectify the query?


select the_date,
next_day( last_day(add_months(the_date,-1)), 'MON') dy,
to_char( the_date, 'dd' ) x,
to_char(next_day(last_day(add_months(the_date,-1)),'MON'),'dd') y,
((to_char( the_date, 'dd'
)-to_char(next_day(last_day(add_months(the_date,-1)),'MON'),'dd'))/7) wk1,
ceil(0.01+(to_char( the_date, 'dd'
)-to_char(next_day(last_day(add_months(the_date,-1)),'MON'),'dd'))/7) wk
from (select to_date( '08/01/2005','mm/dd/yyyy')+level-1 the_date
from dual
connect by level <= 31
)
/



Tom Kyte
September 06, 2005 - 8:52 pm UTC

it is math, have you taken a good hard look at it yourself?

100%

Phil, March 09, 2006 - 12:30 pm UTC

Tom

Great as usual. As an aside for IW, how do I get from 16 to the Monday date (minus the year) please?

I'd like to be able to tell the use that 1 for example is 2nd Jan (this year)

Tom Kyte
March 09, 2006 - 3:33 pm UTC

didn't follow you - can you recap from the start, I don't know the context here (big page)

More Information

Phil, March 10, 2006 - 6:01 am UTC

Hello Tom

Reading my message again this morning I have realised I gave you only cryptic clues as to what I wanted!
I'm using Apex (isn't it great?) and have a page with master detail in it. The master report has a summary per week and the detail lists events on the week. I wanted the date for the week for the detail which I believe I have using the following. The bind is a hidden item which stores the week number 'click' from the master report.

select trunc(sysdate,'YY')+(:P655_WEEK-1)*7 from dual;

This value above is loaded into a hidden item and then used in the report heading using the &P123_HEADING. notation which was another trick I learned yesterday.

Best regards

Phil

Tom Kyte
March 10, 2006 - 12:23 pm UTC

ok, so did you answer your own question? or what is the question?

iso year

Deepak, February 07, 2007 - 10:24 am UTC

Hi Tom,

After executing the follwing query on my Oracle 9.2.0.7 database

SQL> SELECT TO_CHAR(TO_DATE('29-DEC-1997'),'IYYY') FROM DUAL;

TO_C
----
1998

Am getting 1998 as result. Can you please help me in understand why am I getting 1998?


Tom Kyte
February 07, 2007 - 6:47 pm UTC

because that year was such that the last couple of days (according to ISO - a standard) would be considered in the next year.

It is a standard, we do what it says to do.

Reverse of Original Question

Adam, December 01, 2010 - 3:12 pm UTC

Hi Tom, I am looking to do convert the week number to the date range. For example, the output for 02-01-2010 (2nd week of January 2010) needs to be 01/03/2010 - 01/09/2010.

Here is my original query that returns the week number:

select COUNT(distinct PID), TO_CHAR(PCREATED,'WW-MM-YYYY')
from Table_P
where PCREATED >='01-JAN-2010' and PCREATED <'01-DEC-2010'
group by TO_CHAR(PCREATED,'WW-MM-YYYY');

Thank you!
Tom Kyte
December 07, 2010 - 8:57 am UTC

  1* select dt, to_char(dt,'ww') from (select trunc(sysdate,'y')+rownum dt from dual connect by rownum <= 31)
ops$tkyte%ORA11GR2> /

DT        TO
--------- --
02-JAN-10 01
03-JAN-10 01
04-JAN-10 01
05-JAN-10 01
06-JAN-10 01
07-JAN-10 01
08-JAN-10 02
09-JAN-10 02
10-JAN-10 02
11-JAN-10 02
12-JAN-10 02
13-JAN-10 02



I don't understand. Using ww - you do not get jan-2 through jan-9. What math are you doing?

WW is defined this way:

Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.


Week begin and end dates within date range

Lakshmi Pathi, October 10, 2011 - 12:04 pm UTC

Hi Tom,

Is there a way only by SQL to get a week begin and end dates for particular date range?

For example:
January 2011
S M Tu W Th F S
1 2 3 4
5 6 7 8 9 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28 29 30 31

suppose user selects date range between 7th jan 2011 and 30th jan 2011(date range will be dynamic), result should be as below:

week begin date week end date
--------------- -------------
05-jan-2011 11-jan-2011
12-jan-2011 18-jan-2011
19-jan-2011 25-jan-2011
26-jan-2011 01-feb-2011


Is there a way I can do this?

Thanks for your help!
Tom Kyte
October 10, 2011 - 1:33 pm UTC

what planet did that calendar come from?

ops$tkyte%ORA11GR2> !cal 1 2011
    January 2011    
Su Mo Tu We Th Fr Sa
                   1
 2  3  4  5  6  7  8
 9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30 31



assuming you really meant using the normal calendar...

ops$tkyte%ORA11GR2> variable sdate varchar2(20)
ops$tkyte%ORA11GR2> variable edate varchar2(20)
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> exec :sdate := '07-jan-2011'; :edate := '30-jan-2011';

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select next_day( to_date( :sdate, 'dd-mon-yyyy')-7,
  2                   to_char(to_date('19000401','yyyymmdd'),'dy'))+7*(level-1) sdate,
  3         next_day( to_date( :sdate, 'dd-mon-yyyy')-7,
  4                   to_char(to_date('19000401','yyyymmdd'),'dy'))+7*(level-1)+6 edate
  5    from dual
  6   where next_day( to_date( :sdate, 'dd-mon-yyyy')-7,
  7                   to_char(to_date('19000401','yyyymmdd'),'dy'))+7*(level-1) <=
  8                                                                              to_date(:edate,'dd-mon-yyyy')
  9  connect by level <= ceil( (to_date(:edate,'dd-mon-yyyy')-to_date(:sdate,'dd-mon-yyyy'))/7 )+2
 10  /

SDATE     EDATE
--------- ---------
02-JAN-11 08-JAN-11
09-JAN-11 15-JAN-11
16-JAN-11 22-JAN-11
23-JAN-11 29-JAN-11
30-JAN-11 05-FEB-11

ops$tkyte%ORA11GR2> 

Thank you very very much TOM

Lakshmi Pathi, October 12, 2011 - 12:30 pm UTC

Your answer was really helpful to me.

Week begin and end dates within date range

Lakshmi Pathi, October 24, 2011 - 8:45 am UTC

Hi Tom,

Further to the previous question, i do have one more requirement as below:

Is there a way only by SQL to get a week begin date of week where "from date" falls and Week end date of the week where "to date" falls for particular date range?

For example:

January 2011
Su Mo Tu We Th Fr Sa
1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30 31

suppose user selects date range between 7th jan 2011 and 24th jan 2011(date range will be
dynamic), result should be as below:

week begin date week end date
--------------- -------------
02-jan-2011 29-jan-2011

Is there a way I can do this?

Thanks for your help!

Tom Kyte
October 24, 2011 - 10:22 am UTC

hmmm, that you have to ask this question means you don't really know how the prior bit of code I gave you works - do you... or you didn't bother looking at it, you just started using it...

because if you did, you would not be asking this question.. but only because the above query actually does what you ask for (and more, but it does this...)


doesn't it scare you a bit to be using some snippet of code you don't really understand? How can you maintain that, explain that over time? How do you know if it really does what you need it to do? How will you debug that?

ops$tkyte%ORA11GR2> variable sdate varchar2(20)
ops$tkyte%ORA11GR2> variable edate varchar2(20)
ops$tkyte%ORA11GR2> exec :sdate := '07-jan-2011'; :edate := '27-jan-2011';

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select next_day( to_date( :sdate, 'dd-mon-yyyy')-7,
  2                   to_char(to_date('19000401','yyyymmdd'),'dy')) sdate,
  3         next_day( to_date( :edate, 'dd-mon-yyyy')-7,
  4                   to_char(to_date('19000401','yyyymmdd'),'dy'))+6 edate
  5    from dual
  6  /

SDATE     EDATE
--------- ---------
02-JAN-11 29-JAN-11




look familiar?


next_day( to_date( :sdate, 'dd-mon-yyyy')-7,
          to_char(to_date('19000401','yyyymmdd'),'dy')) sdate,



that bit takes your beginning date of 02-jan-2011 and starts by turning it into a date.

It then subtracts 7 days from that giving us 31-dec-2010. We then use a fixed date - april 1st, 1900 - which we know is a sunday to get the 'dy' spelling of sunday in whatever language you are using.

We ask for the next sunday after 31-dec-2010 which is the beginning of the week you wanted.

That is how that works and that is verbatim from the other query I already gave you (minus the 7*(level-1) bit since we only need the first row.. not all of the weeks.


we then do something similar with the end date.



Heck, you could have just taken the query I gave you before and selected min of the first column and max of the second...


did you try this on your own at all? Just wondering....

Very cool

Jim, January 30, 2012 - 9:28 pm UTC

some great examples here Tom, just what I was looking for
thankyou

Query

A reader, March 23, 2012 - 9:33 pm UTC

Hi Tom,

How to achieve this:

If I give a date as parameter for min_date and max_date it gives me a list biweekly range

If I pass the parameter as '05-JAN-2012' and '25-FEB-2015'

the result should be

01-JAN-2012
15-JAN-2012
01-FEB-2012
15-FEB-2012
01-MAR-2012
15-MAR-2012
01-APR-2012
15-APR-2012
...
28-FEB-2015

Please advice.
Tom Kyte
March 24, 2012 - 10:18 am UTC

did you read this page?

we already did that one

hint, just hit page up a few times.

Follow up

A reader, March 24, 2012 - 1:08 pm UTC

My apologies if I did not find that answer. But I could find the next_day solutions.

I was using this solution in reference to what I understood here in this page. Is it fine:

WITH data_dates
     AS (SELECT min(start_date) AS min_start_date,
                max(end_date) AS max_end_date
           FROM your_table_name)
SELECT col_biweekly_dates
  FROM (SELECT col_biweekly_dates, TO_CHAR (col_biweekly_dates, 'dd') cnt_day_in_month, ROWNUM
          FROM (    SELECT min_start_date + (1 * (LEVEL - 1)) AS col_biweekly_dates
                      FROM data_dates
                CONNECT BY LEVEL <=
                              CEIL (
                                 ( (max_end_date - min_start_date) + 1))))
 WHERE cnt_day_in_month IN (1, 15);

Tom Kyte
March 24, 2012 - 4:14 pm UTC

ops$tkyte%ORA11GR2> variable sdate varchar2(20)
ops$tkyte%ORA11GR2> variable edate varchar2(20)
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> exec :sdate := '07-jan-2011'; :edate := '30-jan-2011';

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select next_day( to_date( :sdate, 'dd-mon-yyyy')-7,
  2                   to_char(to_date('19000401','yyyymmdd'),'dy'))+7*(level-1) sdate,
  3         next_day( to_date( :sdate, 'dd-mon-yyyy')-7,
  4                   to_char(to_date('19000401','yyyymmdd'),'dy'))+7*(level-1)+6 edate
  5    from dual
  6   where next_day( to_date( :sdate, 'dd-mon-yyyy')-7,
  7                   to_char(to_date('19000401','yyyymmdd'),'dy'))+7*(level-1) <=
  8                                                                              
to_date(:edate,'dd-mon-yyyy')
  9  connect by level <= ceil( (to_date(:edate,'dd-mon-yyyy')-to_date(:sdate,'dd-mon-yyyy'))/7 )+2
 10  /

SDATE     EDATE
--------- ---------
02-JAN-11 08-JAN-11
09-JAN-11 15-JAN-11
16-JAN-11 22-JAN-11
23-JAN-11 29-JAN-11
30-JAN-11 05-FEB-11

ops$tkyte%ORA11GR2> 


from above

that does weekly, but if you cannot figure out how to make it biweekly.... Well, then you would need to read it and rip it apart till you understand it.

Thanks for answering our doubts

Kumar, June 22, 2012 - 7:20 am UTC

Hi Tom,
I have a query......
i want to generate the week number of year between two given dates. My definition of week is Sunday to Saturday.
start_date = '30/11/12';
end_date = '01/01/13';
I want the output as :
48/2012
49/2012
50/2012
51/2012
52/2012
53/2012
01/2013

Tom Kyte
June 22, 2012 - 8:17 am UTC

where did 53 come from?

ops$tkyte%ORA11GR2> with dates
  2  as
  3  (select to_date( '30/11/12', 'dd/mm/rr' ) sdate, to_date( '01/01/13', 'dd/mm/rr' ) edate
  4     from dual
  5  )
  6  select to_char( the_date, 'iw/yyyy' )
  7    from (
  8  select distinct trunc( sdate+(level-1), 'iw' ) the_date
  9    from dates
 10  connect by level <= edate-sdate+1
 11         )
 12   order by the_date
 13  /

TO_CHAR
-------
48/2012
49/2012
50/2012
51/2012
52/2012
01/2012

6 rows selected.

Thanks for your quick response...It really helped me

Kumar, June 22, 2012 - 8:54 am UTC

Hats off to you sir.....
I have one more query just wanted to enhance my DB skill.
Sample data :

product  date  stages
1  01-May-2012 stage1
1  03-May-2012 stage2
1  04-May-2012 closed
1  05-May-2012 stage2
1  07-May-2012 closed
1  08-May-2012 stage3
1  09-May-2012 closed
2  12-May-2012 stage1
2  15-May-2012 closed
2  17-May-2012 stage2

create table t ( product int, stdate date, stages varchar2(30) );

insert into t values ( 1, to_date( '01-may-2012'), 'stage1' );
insert into t values ( 1, to_date( '03-may-2012'), 'stage2' );
insert into t values ( 1, to_date( '04-may-2012'), 'closed' );
insert into t values ( 1, to_date( '05-may-2012'), 'stage2' );
insert into t values ( 1, to_date( '07-may-2012'), 'closed' );
insert into t values ( 1, to_date( '08-may-2012'), 'stage3' );
insert into t values ( 1, to_date( '09-may-2012'), 'closed' );
insert into t values ( 2, to_date( '12-may-2012'), 'stage1' );
insert into t values ( 2, to_date( '15-may-2012'), 'closed' );
insert into t values ( 2, to_date( '17-may-2012'), 'stage2' );


I want to break this into sets like one set is complete once its closed.
I want output as

product date_ident crntstg crntdt  nxtstg nxtdt
1 01-May-2012 stage1 01-May-2012 stage2 03-May-2012
1 01-May-2012 stage2 03-May-2012 closed 04-May-2012
1 01-May-2012 closed 04-May-2012 stage2 05-May-2012
1 05-May-2012 stage2 05-May-2012 closed 07-May-2012
1 05-May-2012 closed 07-May-2012 stage3 08-May-2012
1 08-May-2012 stage3 08-May-2012 closed 09-May-2012
1 08-May-2012 closed 09-May-2012 NULL NULL
2 12-May-2012 stage1 12-May-2012 closed 15-May-2012
2 12-May-2012 closed 15-May-2012 stage2 17-May-2012
2 17-May-2012 stage2 17-May-2012 NULL NULL

The nextstage and nextdt is based on lead of stage on product and order by stdate.
i want your help in including second column date_ident based on product and prevstage.
first record is product 1 and prevstage is null then put date_ident as stdate of current row and the date 
will be same till i encounter the closed for 1. since 4th record is product 1 and
prevstage is closed then reset again the date_ident as stdate(means date_ident will be reset 
if prevstage is null or closed.)
I don't know whether this is possible or not but i am quite sure that you would have some solution....
Thanks a lot in advance

Tom Kyte
June 22, 2012 - 4:53 pm UTC

ops$tkyte%ORA11GR2> select product,
  2         last_value(date_ident_1 ignore nulls) over (partition by product order by stdate) date_ident,
  3         stdate, stages, next_stdate, next_stage
  4    from (
  5  select product, stdate, stages,
  6         lead(stdate) over (partition by product order by stdate) next_stdate,
  7         lead(stages) over (partition by product order by stdate) next_stage,
  8         case when nvl(lag(stages) over (partition by product order by stdate),'closed') = 'closed'
  9              then stdate
 10          end date_ident_1
 11    from t
 12         )
 13  /

   PRODUCT DATE_IDEN STDATE    STAGES                         NEXT_STDA NEXT_STAGE
---------- --------- --------- ------------------------------ --------- ------------------------------
         1 01-MAY-12 01-MAY-12 stage1                         03-MAY-12 stage2
         1 01-MAY-12 03-MAY-12 stage2                         04-MAY-12 closed
         1 01-MAY-12 04-MAY-12 closed                         05-MAY-12 stage2
         1 05-MAY-12 05-MAY-12 stage2                         07-MAY-12 closed
         1 05-MAY-12 07-MAY-12 closed                         08-MAY-12 stage3
         1 08-MAY-12 08-MAY-12 stage3                         09-MAY-12 closed
         1 08-MAY-12 09-MAY-12 closed
         2 12-MAY-12 12-MAY-12 stage1                         15-MAY-12 closed
         2 12-MAY-12 15-MAY-12 closed                         17-MAY-12 stage2
         2 17-MAY-12 17-MAY-12 stage2

10 rows selected.


Confused

STEVEN HOLMES, June 22, 2012 - 3:42 pm UTC

Tom,
In your followup post above, (Followup June 22, 2012 - 8am Central time zone:) why is the last date 01/2012? Shouldn't it be 01/2013?

Thanks
Tom Kyte
June 22, 2012 - 5:10 pm UTC

arg, that is because I did the trunc, it moved 1-jan-2013 into december. December 31st is in the first week of 2012 technically using IW.

maybe we should use ww instead - it'll depend on their needs (see the sql reference for how ww and iw work)


ops$tkyte%ORA11GR2> with dates
  2  as
  3  (select to_date( '30/11/12', 'dd/mm/rr' ) sdate, to_date( '01/01/13', 'dd/mm/rr' ) edate
  4     from dual
  5  )
  6  select to_char( the_date, 'ww/yyyy' )
  7    from (
  8  select distinct trunc( sdate+(level-1), 'ww' ) the_date
  9    from dates
 10  connect by level <= edate-sdate+1
 11         )
 12   order by the_date
 13  /

TO_CHAR
-------
48/2012
49/2012
50/2012
51/2012
52/2012
53/2012
01/2013

7 rows selected.

Thanks a lot....

Kumar, June 24, 2012 - 12:09 am UTC


Anupam, October 03, 2012 - 4:11 am UTC

Hi Tom,

If my table contains the daily data then how can I extract the weekly data from the table?
Tom Kyte
October 09, 2012 - 11:47 am UTC

that is something you would have to tell us.

think about it, how could anyone tell you how to query your own data given we know nothing about your data - what attributes there are, what identifies a given days data, nothing.

Muhammad Saqib, October 10, 2012 - 2:12 am UTC

Dear Tom,
I want to display a data in date range with week of month.
Suppose For October 2012 the data would be look like
Start_Date End_Date Week
01-OCT-12 07-OCT-12 1
08-OCT-12 14-OCT-12 2

I have to stop it on week completion. as today is 10-OCT-12 then it will just display 1st line.
Tom Kyte
October 10, 2012 - 7:46 am UTC

how do you define a week? what are your rules for a 'week' what would the weeks in November 2012 be?



(if you have the above data somewhere, getting it to stop is rather trivial, "where end_date <= sysdate"

Saqib, June 22, 2013 - 10:17 am UTC

Hi Tom,

I'm having an issue to resolve. I want This type of result

Month First_Date Last_date Week
Jan-2013 01-jan-2013 07-jan-2013 1
Jan-2013 08-jan-2013 14-jan-2013 2
Jan-2013 15-jan-2013 21-jan-2013 3
Jan-2013 22-jan-2013 31-jan-2013 4
Feb-2013 01-feb-2013 07-feb-2013 1
Feb-2013 08-feb-2013 14-feb-2013 2
Feb-2013 15-feb-2013 21-feb-2013 3
Feb-2013 22-feb-2013 28-feb-2013 4
.
.
.
And so on.

Please help me out of this.
Tom Kyte
July 01, 2013 - 5:40 pm UTC

you have weeks that are not 7 days long? interesting.



ops$tkyte%ORA11GR2> with months
  2  as
  3  (select to_date( level, 'mm' ) mm
  4     from dual
  5  connect by level <= 12
  6  ),
  7  weeks
  8  as
  9  (select level wk
 10     from dual
 11  connect by level <= 4
 12  )
 13  select mm,
 14         mm + (wk-1)*7,
 15             case when wk < 4
 16              then mm + wk*7-1
 17                          else last_day(mm)
 18             end,
 19             wk
 20    from months, weeks
 21  /

MM        MM+(WK-1) CASEWHENW         WK
--------- --------- --------- ----------
01-JAN-13 01-JAN-13 07-JAN-13          1
01-JAN-13 08-JAN-13 14-JAN-13          2
01-JAN-13 15-JAN-13 21-JAN-13          3
01-JAN-13 22-JAN-13 31-JAN-13          4
01-FEB-13 01-FEB-13 07-FEB-13          1
01-FEB-13 08-FEB-13 14-FEB-13          2
01-FEB-13 15-FEB-13 21-FEB-13          3
01-FEB-13 22-FEB-13 28-FEB-13          4
01-MAR-13 01-MAR-13 07-MAR-13          1
....

A reader, July 02, 2013 - 8:59 am UTC

Hi Tom, you are great.

I am getting the week num by using this script as you told
trunc( (process_end_dt-next_day(trunc(process_end_dt,'mm')-8, 'sun'))/7 )+1 hst_week_seq

But in addition to it i want to implement some logic. what i am trying to do is ,I am trying to get the 6mnths data where the data will refresh once in a week that is every satday. I have got week seq num using above code.But the problem is when the week num is repeating twice that means if they refresh more than once in a week then i want to take only week no for latest refreshed date and when ever they missed to refresh the data in a week then i want to take previous week no by adding one to it.

currently i am getting like this
refreshed_date week_num
3/2/2013 8:10 1
3/9/2013 5:24 2
3/16/2013 6:14 3
3/25/2013 8:32 5
3/30/2013 13:15 5
4/6/2013 7:18 1
4/14/2013 15:18 2
and so on for 6months


Output should be like
refreshed_date week_num
3/2/2013 8:10 1
3/9/2013 5:24 2
3/16/2013 6:14 3
3/25/2013 8:32 4
3/30/2013 13:15 5
4/6/2013 7:18 1
4/14/2013 15:18 2
and so on for 6months


Please help me


Tom Kyte
July 02, 2013 - 5:08 pm UTC

no creates
no inserts
no look

and you need to explain this better, I don't really know what I'm looking at there at all. explain the detailed logic behind how you arrive at your numbers.


Need to find month, week and year between two given dates

Reader, July 10, 2013 - 10:59 am UTC

Hi Tom,
I found this site most interesting and useful. I got a question to ask regarding date ranges and weeks, months and year between two given dates.

start_date = 03-Mar-2012
end_date = 05-May-2013

And the definition of week, month and year are as follows :- 
1)For week i need output as:- date as on monday for every week

week  date
09/2012  26/02/2012 00:01AM ==>will alwaysbe current/prev Monday for running IW Week
10/2012  05/03/2012 00:01AM
............
19/2013  29/04/2013 00:01AM

2)For month i need output as:-
month  date
03/2012  01/03/2012 00:01AM ==>will always be first day of month 00:01AM
04/2012  01/04/2012 00:01AM
.............
05/2013  01/05/2013 00:01AM

3)For year i need output as:-
year  date
2012  01/01/2012 00:01AM ==>will always be first day of year 00:01AM
2013  01/01/2013 00:01AM

Thanks a lot in advance......

Tom Kyte
July 16, 2013 - 3:30 pm UTC

this makes no sense.

your output - the date 26/02/2012 is a sunday. The week for 29/04/2013 should be 18. please explain.


and start working with a query that looks like:


ops$tkyte%ORA11GR2> with data
  2  as
  3  (
  4  select next_day( to_date( :sdate, 'dd-mon-yyyy' )-7, 'mon' ) sdate,
  5         to_date( :edate, 'dd-mon-yyyy' ) edate
  6    from dual
  7  )
  8  select to_char( sdate + 7*(level-1), 'iw/yyyy' ), to_char( sdate+7*(level-1), 'dd/mm/yyyy' )
  9    from data
 10  connect by level <= ceil( (edate - sdate)/7 )+1
 11  /

TO_CHAR TO_CHAR(SD
------- ----------
09/2012 27/02/2012
10/2012 05/03/2012
11/2012 12/03/2012
....

16/2013 15/04/2013
17/2013 22/04/2013
18/2013 29/04/2013
19/2013 06/05/2013

63 rows selected.




understand what it does and how is does it and see if you cannot get the other two answers yourself!

getting an error when I used the code to fetch the week number

ransree, June 30, 2014 - 6:42 pm UTC

I used the below code from your inputs to calculate the weeknum:
SELECT (decode(trunc(x), trunc(x,'mm'), 1,trunc(TO_DATE(x)-next_day(trunc(x,'mm')-8, 'sun'))/7 )+1)

but I am facing an error
Invalid arguments to function 'trunc' : 'Incorrect argument datatypes (INTERVAL DAY TO SECOND) to
function 'trunc'

I am giving input x as datetime function something like 2014-06-30 00:00:00.0

Please help me tom