Excelent function...
A reader, April 17, 2001 - 1:17 pm UTC
It's very useful. I want more information about date formats in Oracle.
Ch Srinivasa Rao, April 17, 2001 - 11:22 pm UTC
This SQL is more usefull because this is a undocumented source.
Helena Markova, April 18, 2001 - 7:17 am UTC
First an last day for any week in any month
Dirk Brouwer, October 09, 2002 - 7:48 am UTC
Very usefull, but from now on I would use:
select trunc(sysdate,'iw'), trunc(sysdate,'iw')+6, last_day(sysdate) from dual, because this year (2002) the first day of the week is tuesday, when you're using 'w'.
Dirk Brouwer, February 03, 2003 - 3:49 am UTC
Be carefull with my last remark:
> "select trunc(sysdate,'iw'), trunc(sysdate,'iw')+6,
> last_day(sysdate) from dual,
> because this year (2002) the first day of the week
> is tuesday, when you're using 'w'."
There's a bug in Oracle7. This year (2003) on a saterday it is selecting the next week instead of this week.
It's working allright in Oracle8.
Calendar Query in Oracle
D Porter, May 27, 2003 - 12:25 pm UTC
Here is a query that was modified from the queries put together by Tom. It does its own calculation of the week (based on Sunday being the first day of the week). The month and year dates are entered and it generates the result:
select max(decode( to_char(dt,'d'), 1, to_char(dt,'dd'), null )) Su,
max(decode( to_char(dt,'d'), 2, to_char(dt,'dd'), null )) Mo,
max(decode( to_char(dt,'d'), 3, to_char(dt,'dd'), null )) Tu,
max(decode( to_char(dt,'d'), 4, to_char(dt,'dd'), null )) We,
max(decode( to_char(dt,'d'), 5, to_char(dt,'dd'), null )) Th,
max(decode( to_char(dt,'d'), 6, to_char(dt,'dd'), null )) Fr,
max(decode( to_char(dt,'d'), 7, to_char(dt,'dd'), null )) Sa
from ( select trunc(to_date('2003-05-15'),'year')+rownum-1 dt,
trunc((rownum + to_char(trunc(to_date('2003-05-15'), 'year'), 'd') - 1 ) / 7 + .9999) weekofyear
from all_objects
where rownum <= to_number((to_char(add_months(trunc(to_date('2003-05-01'), 'mm'), 1), 'ddd') - 1))
) calendar
where to_char(dt, 'mm') = to_char(to_date('2003-05-15'), 'mm')
group by weekofyear
order by weekofyear
A reader, October 28, 2003 - 11:01 am UTC
Hi Tom,
could you please tell us the difference between 'w' and 'iw' formats.
Thanks
October 28, 2003 - 2:10 pm UTC
documented in the sql reference guide, under DATE FORMATS.
just use different "standards".
A reader, October 28, 2003 - 2:35 pm UTC
Hi Tom,
I just went through the documentation and I just have one doubt here, if i need to get the date of "MONDAY" (that is the first day of the week) of any week to which a particular date belongs should i use 'w' or 'iw'.
Would :-
Select trunc('any_date', 'iw') from dual
will always return me the first day (MONDAY) of the week.
October 28, 2003 - 8:59 pm UTC
IW Same day of the week as the first day of the ISO year
W Same day of the week as the first day of the month
seems to work
ops$tkyte@ORA920LAP> l
1 select dt,
2 trunc(dt+30,'iw'),
3 next_day( (dt+30)-7, 'MON'),
4 trunc(dt+30,'w')
5 from (select add_months( to_date('01-jan-2003','dd-mon-yyyy'), 12*(rownum-1) ) dt
6 from all_objects
7* where rownum <= 20 )
ops$tkyte@ORA920LAP> /
DT TRUNC(DT+30,'IW NEXT_DAY((DT+30 TRUNC(DT+30,'W'
--------------- --------------- --------------- ---------------
Wed 01-jan-2003 Mon 27-jan-2003 Mon 27-jan-2003 Wed 29-jan-2003
Thu 01-jan-2004 Mon 26-jan-2004 Mon 26-jan-2004 Thu 29-jan-2004
Sat 01-jan-2005 Mon 31-jan-2005 Mon 31-jan-2005 Sat 29-jan-2005
Sun 01-jan-2006 Mon 30-jan-2006 Mon 30-jan-2006 Sun 29-jan-2006
Mon 01-jan-2007 Mon 29-jan-2007 Mon 29-jan-2007 Mon 29-jan-2007
Tue 01-jan-2008 Mon 28-jan-2008 Mon 28-jan-2008 Tue 29-jan-2008
Thu 01-jan-2009 Mon 26-jan-2009 Mon 26-jan-2009 Thu 29-jan-2009
Fri 01-jan-2010 Mon 25-jan-2010 Mon 25-jan-2010 Fri 29-jan-2010
Sat 01-jan-2011 Mon 31-jan-2011 Mon 31-jan-2011 Sat 29-jan-2011
Sun 01-jan-2012 Mon 30-jan-2012 Mon 30-jan-2012 Sun 29-jan-2012
Tue 01-jan-2013 Mon 28-jan-2013 Mon 28-jan-2013 Tue 29-jan-2013
Wed 01-jan-2014 Mon 27-jan-2014 Mon 27-jan-2014 Wed 29-jan-2014
Thu 01-jan-2015 Mon 26-jan-2015 Mon 26-jan-2015 Thu 29-jan-2015
Fri 01-jan-2016 Mon 25-jan-2016 Mon 25-jan-2016 Fri 29-jan-2016
Sun 01-jan-2017 Mon 30-jan-2017 Mon 30-jan-2017 Sun 29-jan-2017
Mon 01-jan-2018 Mon 29-jan-2018 Mon 29-jan-2018 Mon 29-jan-2018
Tue 01-jan-2019 Mon 28-jan-2019 Mon 28-jan-2019 Tue 29-jan-2019
Wed 01-jan-2020 Mon 27-jan-2020 Mon 27-jan-2020 Wed 29-jan-2020
Fri 01-jan-2021 Mon 25-jan-2021 Mon 25-jan-2021 Fri 29-jan-2021
Sat 01-jan-2022 Mon 31-jan-2022 Mon 31-jan-2022 Sat 29-jan-2022
20 rows selected.
but i don't know what the ISO year "means" frankly
A reader, October 29, 2003 - 12:43 pm UTC
Hi Tom,
Wouldn't trunc('my_date', 'd')+1 be appropriate here?
Please comment.
October 29, 2003 - 2:22 pm UTC
only if the first day of your week is sunday -- believe that it'll depend on your NLS settings.
ADD_MONTHS: transition to next year
Alex, December 06, 2004 - 5:27 am UTC
Hi Tom, D Porter
query posted D Porter does't work respectively
ERROR: transition to next year - in December
where rownum <= to_number((to_char(add_months(trunc(to_date('2004-12-12'), 'mm'), 1), 'ddd') - 1))
= 0 rows in December
select max(decode( to_char(dt,'d'), 1, to_char(dt,'dd'), null )) Su,
max(decode( to_char(dt,'d'), 2, to_char(dt,'dd'), null )) Mo,
max(decode( to_char(dt,'d'), 3, to_char(dt,'dd'), null )) Tu,
max(decode( to_char(dt,'d'), 4, to_char(dt,'dd'), null )) We,
max(decode( to_char(dt,'d'), 5, to_char(dt,'dd'), null )) Th,
max(decode( to_char(dt,'d'), 6, to_char(dt,'dd'), null )) Fr,
max(decode( to_char(dt,'d'), 7, to_char(dt,'dd'), null )) Sa
from ( select trunc(to_date('28-DEC-2008'),'year')+rownum-1 dt,
trunc((rownum + to_char(trunc(to_date('28-DEC-2008'), 'year'),'d') - 1 ) / 7 + .9999) weekofyear
from all_objects
where rownum <= to_number((to_char(trunc(to_date('28-DEC-2008'), 'mm'), 'ddd') -1)) +31
) calendar
where to_char(dt, 'mm') = to_char(to_date('28-DEC-2008'), 'mm')
group by weekofyear
order by weekofyear;
with kind regards
Why this?
A reader, July 28, 2005 - 6:29 pm UTC
Tom,
Why I am not getting SUNDAY?:
1* select trunc(to_date('29-JUL-2005','DD-MON-YYYY'),'IW') from dual
SQL> /
TRUNC(TO_
---------
25-JUL-05
SQL> !cal
July 2005
Sun Mon Tue Wed Thu Fri Sat
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
PS: I am on 9ir2
Thanks
July 29, 2005 - 8:15 am UTC
ops$tkyte@ORA9IR2> select to_char( to_date('24-jul-2005','dd-mon-yyyy'), 'IW' ) from dual;
TO
--
29
ops$tkyte@ORA9IR2> select to_char( to_date('25-jul-2005','dd-mon-yyyy'), 'IW' ) from dual;
TO
--
30
ops$tkyte@ORA9IR2>
because they are in different "IW's"
Your thoughts
Joe, August 31, 2005 - 2:44 pm UTC
Tom,
We have a bunch of statements like the following:
....WHERE TRUNC(tall_number) = number_IN
any thoughts on this... I know a FBI will be good in
tall_number. I am looking for best practices.
NLS setting for Day of week
Bala, July 05, 2006 - 9:17 am UTC
Hi Tom,
What is the NLS setting to change the Day of week?
July 08, 2006 - 7:57 am UTC
I don't know what you mean by that.
Normally, we wait for tomorrow to change the day of the week...
LOL!
Michel Cadot, July 08, 2006 - 8:26 am UTC
good, but ... howto from IW to date?
Julio, August 04, 2006 - 7:06 am UTC
I mean, this is a very good way to get a week from a date, but what I need is to get the first day from an iso week (we make here some summaries by iso week).
Thus, the client asks for data of the week 31 of year 2006 and I have to build the query with "between '31/07/2006' and '07/08/2006'"
I tried this:
Select To_date('312006','IWYYYY') from dual;
but I get an error: ORA-01820
I expected to get
What I need is a conversion function from week/year to date (truncating to the first day of that iso week).
August 04, 2006 - 8:29 am UTC
ops$tkyte%ORA10GR2> variable x number
ops$tkyte%ORA10GR2> exec :x := 31
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select years,
2 trunc( trunc(years,'y') + (:x + (case when to_char(trunc(years,'y'),'iw')='01' then -1 else 0 end))*7, 'iw') begin_date ,
3 to_char( trunc( trunc(years,'y') + (:x + (case when to_char(trunc(years,'y'),'iw')='01' then -1 else 0 end))*7, 'iw'), 'iw' ) begin_date_iw
4 from (
5 select add_months(sysdate,12*(rownum-1)) years
6 from all_users
7 )
8 /
YEARS BEGIN_DAT BE
--------- --------- --
04-AUG-06 31-JUL-06 31
04-AUG-07 30-JUL-07 31
04-AUG-08 28-JUL-08 31
04-AUG-09 27-JUL-09 31
04-AUG-10 02-AUG-10 31
04-AUG-11 01-AUG-11 31
04-AUG-12 30-JUL-12 31
04-AUG-13 29-JUL-13 31
04-AUG-14 28-JUL-14 31
04-AUG-15 27-JUL-15 31
04-AUG-16 01-AUG-16 31
04-AUG-17 31-JUL-17 31
04-AUG-18 30-JUL-18 31
04-AUG-19 29-JUL-19 31
04-AUG-20 27-JUL-20 31
04-AUG-21 02-AUG-21 31
04-AUG-22 01-AUG-22 31
04-AUG-23 31-JUL-23 31
04-AUG-24 29-JUL-24 31
04-AUG-25 28-JUL-25 31
04-AUG-26 27-JUL-26 31
04-AUG-27 02-AUG-27 31
04-AUG-28 31-JUL-28 31
04-AUG-29 30-JUL-29 31
04-AUG-30 29-JUL-30 31
04-AUG-31 28-JUL-31 31
04-AUG-32 26-JUL-32 31
04-AUG-33 01-AUG-33 31
04-AUG-34 31-JUL-34 31
04-AUG-35 30-JUL-35 31
04-AUG-36 28-JUL-36 31
04-AUG-37 27-JUL-37 31
04-AUG-38 02-AUG-38 31
04-AUG-39 01-AUG-39 31
34 rows selected.
good, but ... howto from IW to date?
Julio, August 07, 2006 - 2:52 am UTC
I mean, this is a very good way to get a week from a date, but what I need is to get the first day from an iso week (we make here some summaries by iso week).
Thus, the client asks for data of the week 31 of year 2006 and I have to build the query with "between '31/07/2006' and '07/08/2006'"
I tried this:
Select To_date('312006','IWYYYY') from dual;
but I get an error: ORA-01820
I expected to get
What I need is a conversion function from week/year to date (truncating to the first day of that iso week).
August 07, 2006 - 8:05 am UTC
I gave you the code to get the first day of the week????? it is right there???
trunc( trunc(years,'y') + (:x + (case when
to_char(trunc(years,'y'),'iw')='01' then -1 else 0 end))*7, 'iw')
that gives you the :x'th week of the YEAR in YEARS.
Database conversion function from iw to date.
Julio, August 07, 2006 - 4:00 am UTC
Apologies for the duplicate post!
I think I've found what I was looking for (a conversion function from iso week to date):
Create or replace function
Fb_Week_Beginning (week integer Default To_char(sysdate,'IW'), year integer Default To_char(sysdate,'YYYY')) Return Date is
Begin
Return next_day( to_date( '04-01-' || year, 'dd-mm-yyyy' ) + (week-2)*7, 'lun');
End;
/
* note 'lun' works for spanish, it has to vary in other langs (I tried with an integer, but it works only in sqlplus, not in database functions).
Anyway I still think that this:
SQL> Select To_date('31/2006','IW/YYYY') from dual;
Should return:
31/07/2006
Just the same way this do:
SQL> select to_date('10/2006','MM/YYYY') FROM DUAL;
TO_DATE(
--------
01/10/06
If it works for months ... why it doesn't for weeks?
August 07, 2006 - 8:06 am UTC
I gave you the simple function right above....
Calculate First Day of Month and First Day of Week based on Sunday as the First day
Alan Kendall, December 12, 2006 - 6:21 pm UTC
The following gives the first day of the month:
select trunc(last_day(last_day(sysdate)-45))+1 first_day_of_month from dual;
The following gives the first day of the week based on Sunday being the first day:
select trunc(sysdate+1,'iw')-1 sunday_as_first_day_of_week from dual;
"the first "+1" makes it not round down a week, the second "-1" makes Sunday first day of week."
;
December 12, 2006 - 10:09 pm UTC
ops$tkyte%ORA9IR2> select trunc(sysdate,'mm'), next_day(trunc(sysdate,'mm')-1,'sun') from dual;
TRUNC(SYS NEXT_DAY(
--------- ---------
01-DEC-06 03-DEC-06
Coverting from week number to date
mc, September 20, 2011 - 3:25 pm UTC
Tom,
We have values stored in a table in the format ww/yyyy where ww is the week number of the year and yyyy is the year, based on this how can I convert this to a date ? I would be interested in getting the first day of the week.
Thanks
mc
Very useful
aa, June 19, 2019 - 9:40 am UTC
really it was helpful
Om, July 28, 2020 - 6:57 am UTC
Hi Tom,
How can i get the following?
1) First day, last day of current week - Sunday being first day - Saturday being last
2) First day, last day of current week - Monday being first day - Firday being last
Thanks!
July 28, 2020 - 8:10 am UTC
truncating a date to the week gives you the monday, so...
SQL> select trunc(sysdate,'IW')-1 from dual;
TRUNC(SYS
---------
26-JUL-20