Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Toufiq.

Asked: March 03, 2016 - 8:49 am UTC

Last updated: December 06, 2021 - 3:08 pm UTC

Version: 9i

Viewed 100K+ times! This question is

You Asked

Hi ASK TOM Team!

I am a bit confused with the iw option from to_char function in SQL. I'm using version 9i and was looking for function to find the week of the year.
I've noticed that the week of year for 01-Jan-2016 is 53 which I expected to be 1. And for 08-Jan-2016 it is 1 which I expected to be 2.
I'm looking for information to understand how this works, and what options are available to rectify this. Below is the link for the output of the SQL statements.

select to_char(to_date('20160101','YYYYMMDD'),'iw') from dual;
53

select to_char(to_date('20160108','YYYYMMDD'),'iw') from dual;
01

Code:
https://livesql.oracle.com/apex/livesql/s/cyg2xk364h9m8bgby4ufv590k

P.S. This is my first question on AskTom!

Thanks in Advance
Toufiq

and Chris said...

Welcome to Ask Tom! Thanks for providing a clear question with a working test case :)

The format mask IW returns the ISO week number of the year. This starts at 1 for the week containing the first Thursday. In 2016 this is the week starting Monday 4th Jan. For more info, see:

https://en.wikipedia.org/wiki/ISO_week_date

To get the number of the year starting at 1 Jan, use WW:

select to_char(to_date('20160101','YYYYMMDD'),'ww') from dual;

TO
--
01

select to_char(to_date('20160108','YYYYMMDD'),'ww') from dual;

TO
--
02

Rating

  (8 ratings)

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

Comments

I have never understood the use case for WW

Kim Berg Hansen, March 04, 2016 - 12:06 pm UTC

IW goes together with IYYY.
In ISO standard "Week 53-2015" goes from Monday 2015-12-28 to Sunday 2016-01-03.
Then the first week of 2016 begins Monday 2016-01-04.

WW starts January 1st, but not following Mon-Sun weeks or Sun-Sat weeks, but weeks using WW shift on the day that is January 1st, so using WW in 2016 makes weeks go from Fri-Thur.

Finding the week number according to US standard - well as far as I know such a format model element does not exist? Or maybe I have just never encountered it?

with dates as (
   select date '2016-01-01' + level - 1 dt
   from dual connect by level <= 14
)
select to_char(dt, 'YYYY-MM-DD dy "IYYY"=IYYY "IW="IW "WW="WW') output
from dates
order by dt;

OUTPUT
---------------------------------------------
2016-01-01 fri IYYY=2015 IW=53 WW=01
2016-01-02 sat IYYY=2015 IW=53 WW=01
2016-01-03 sun IYYY=2015 IW=53 WW=01
2016-01-04 mon IYYY=2016 IW=01 WW=01
2016-01-05 tue IYYY=2016 IW=01 WW=01
2016-01-06 wed IYYY=2016 IW=01 WW=01
2016-01-07 thu IYYY=2016 IW=01 WW=01
2016-01-08 fri IYYY=2016 IW=01 WW=02
2016-01-09 sat IYYY=2016 IW=01 WW=02
2016-01-10 sun IYYY=2016 IW=01 WW=02
2016-01-11 mon IYYY=2016 IW=02 WW=02
2016-01-12 tue IYYY=2016 IW=02 WW=02
2016-01-13 wed IYYY=2016 IW=02 WW=02
2016-01-14 thu IYYY=2016 IW=02 WW=02

14 rows selected.


Regards
Kim Berg Hansen
Chris Saxon
March 04, 2016 - 1:57 pm UTC

Anyone who wants their weeks to start on 1 Jan? It's not a situation I've come across to be honest.

I'm not aware of a format model using the US standard either. ISO all the way! ;)

I need the week of JAN 1 to build a calendar

Mike, October 18, 2017 - 6:56 pm UTC

Adding this to my select
******************
CASE WHEN
TO_CHAR(TO_DATE(A.START_DT,'YYYY-MM-DD'),'D') < TO_CHAR(TO_DATE( :1 || '-01-01' ,'YYYY-MM-DD'),'D')
THEN
TO_CHAR(TO_CHAR(TO_DATE(A.START_DT,'YYYY-MM-DD'),'WW') + 1)
ELSE
TO_CHAR(TO_CHAR(TO_DATE(A.START_DT,'YYYY-MM-DD'),'WW') + 0)
END
******************

I can then drop the results into a BI Pub and out comes a calendar grouped by month then week and I can see how many hours employees are scheduled for across a calendar year.

PSOFT HR slanted answer.

The full select I used:
**********************************************
SELECT TO_CHAR(TO_DATE( TO_CHAR(A.START_DT,'YYYY-MM-DD'),'YYYY-MM-DD'),'YYYY'), TO_CHAR(TO_DATE( TO_CHAR(A.START_DT,'YYYY-MM-DD'),'YYYY-MM-DD'),'MM'), CASE WHEN
TO_CHAR(TO_DATE( TO_CHAR(A.START_DT,'YYYY-MM-DD'),'YYYY-MM-DD'),'D') < TO_CHAR(TO_DATE( ':1' || '-01-01' ,'YYYY-MM-DD'),'D')
THEN
TO_CHAR(TO_CHAR(TO_DATE( TO_CHAR(A.START_DT,'YYYY-MM-DD'),'YYYY-MM-DD'),'WW') + 1)
ELSE
TO_CHAR(TO_CHAR(TO_DATE( TO_CHAR(A.START_DT,'YYYY-MM-DD'),'YYYY-MM-DD'),'WW') + 0)
END, TO_CHAR(TO_DATE( TO_CHAR(A.START_DT,'YYYY-MM-DD'),'YYYY-MM-DD'),'d'), TO_CHAR(A.START_DT,'YYYY-MM-DD'), B.SCHEDULE_ID, B.SCHED_HRS
FROM PS_TL_CALENDAR A, PS_SCH_DEFN_DTL B, PS_SCH_DEFN_TBL C
WHERE ( A.PERIOD_ID = 'PSDAY'
AND B.EFFDT =
(SELECT MAX(B_ED.EFFDT) FROM PS_SCH_DEFN_DTL B_ED
WHERE B.SETID = B_ED.SETID
AND B.SCH_ADHOC_IND = B_ED.SCH_ADHOC_IND
AND B.SCHEDULE_ID = B_ED.SCHEDULE_ID
AND B_ED.EFFDT <= A.START_DT)
AND B.SCH_ADHOC_IND = C.SCH_ADHOC_IND
AND B.SCHEDULE_ID = C.SCHEDULE_ID
AND C.EFFDT =
(SELECT MAX(C_ED.EFFDT) FROM PS_SCH_DEFN_TBL C_ED
WHERE C.SETID = C_ED.SETID
AND C.SCH_ADHOC_IND = C_ED.SCH_ADHOC_IND
AND C.SCHEDULE_ID = C_ED.SCHEDULE_ID
AND C_ED.EFFDT <= A.START_DT)
AND B.DAYNUM = (MOD((TO_DATE( A.START_DT,'YYYY-MM-DD') - TO_DATE( C.EFFDT,'YYYY-MM-DD')) , C.SCHEDULE_DAYS))+1
AND ( B.SCHEDULE_ID LIKE 'BAT%'
OR B.SCHEDULE_ID LIKE 'PLAT%')
AND TO_CHAR(TO_DATE( A.START_DT,'YYYY-MM-DD'),'YYYY') = :1 )
ORDER BY 5, 6
**********************************************

This is what I do to get the week number

A reader, May 14, 2019 - 9:55 pm UTC

This is what I do to get the week number assuming the week starts on Sunday.

select count(output) + 1 as weekno from (
with dates as (
select date '2019-01-01' + level - 1 dt
from dual connect by level <= to_char(to_date('05/15/2019','MM/DD/YYYY'),'ddd')
)
select to_char(dt, 'day' ) output
from dates )
where trim(output) = 'sunday'

Chris Saxon
May 15, 2019 - 1:39 pm UTC

So do the days between 1 Jan and the first Sunday fall on week 0, 1, or 53 of the previous year?

Also, this reports 1 Jan on years that start on a Sunday as week 2...

select count(output) + 1 as weekno from (
with dates as (
select date '2006-01-01' + level - 1 dt
from dual connect by level <= to_char(to_date('01/01/2006','MM/DD/YYYY'),'ddd')
)
select to_char(dt, 'day' ) output
from dates )
where trim(output) = 'sunday'

WEEKNO   
        2 

Query for getting fiscal week

A reader, July 01, 2020 - 2:34 pm UTC

Query for getting fiscal week as week starts saturday to sunday for both leap and non-leap year?

Query for getting fiscal week

Vignesh, July 02, 2020 - 11:27 am UTC

Can Anyone give me a query to get fiscal week as week starts from Saturday to Sunday for both leap and non-leap year. Quick response will be very helpful

Regards
Vignesh
Chris Saxon
July 02, 2020 - 3:54 pm UTC

Asking multiple times won't get your question answered quicker.

And I'm confused.

How does a week run from Saturday to Sunday? That's only two days? What exactly are you expecting to see? Please clarify with examples.

Vignesh, July 03, 2020 - 3:56 am UTC

Thanks for your review,

This is the business requirement that in particular site, they are considering the Fiscal week or Financial week. So here every week starts from Saturday and ends with Sunday. In sql date function we have to_char(sysdate,'WW') or to_char(sysdate,'IW'). But this will not work here as It will be as ISO week so here week starts from Sunday to Saturday. So I need query to get Fiscal week

For eg., I can able to get week for leap year as 2020 is leap year so below query will work.
SELECT CEIL((to_char(to_date(<date for week>,'DD-MON-RRRR'),'ddd')+to_char(trunc(sysdate,'year'),'d'))/7)  FROM dual


This will not work for non-leap year as 2021. Will you please advise.
Chris Saxon
July 03, 2020 - 10:05 am UTC

How can the week start with Saturday and end on Sunday?! That's either a 2 day or 8 day week!

ISO weeks run Monday - Sunday. You can convert these to Saturday - Friday weeks by either adding 2 or subtracting 5 from the date, then converting to the ISO week.

Whether you add or subtract depends on which day you consider to be the start of the fiscal year:

select to_char ( date'2020-01-01' + level, 'day' ) dy,
       to_char ( date'2020-01-01' + level - 5, 'iw' ) fw,
       date'2020-01-01' + level dt
from   dual
connect by level <= 10;

DY           FW    DT                     
thursday     52    02-JAN-2020 00:00:00    
friday       52    03-JAN-2020 00:00:00    
saturday     01    04-JAN-2020 00:00:00    
sunday       01    05-JAN-2020 00:00:00    
monday       01    06-JAN-2020 00:00:00    
tuesday      01    07-JAN-2020 00:00:00    
wednesday    01    08-JAN-2020 00:00:00    
thursday     01    09-JAN-2020 00:00:00    
friday       01    10-JAN-2020 00:00:00    
saturday     02    11-JAN-2020 00:00:00


I'm also not clear how leap years changes the calculation. Please provide examples:

1 Jan 2020 => FW ???
2 Jan 2020 => FW ???
4 Jan 2020 => FW ??? (1st Sat in the year)
...
31 Dec 2020 => FW ???
1 Jan 2021 => FW ???
etc.

Vignesh, July 03, 2020 - 11:06 am UTC

Sorry It's my mistake, Here will be clarified that week expected fiscal starts as Saturday and ends with Friday.

As given earlier the below query will works for this 2020 leap year.

SELECT CEIL((to_char(to_date(<date for week>,'DD-MON-RRRR'),'ddd')+to_char(trunc(sysdate,'year'),'d'))/7) FROM dual.

Date Fiscal week

1 Jan 2020 => 1
2 Jan 2020 => 1
4 Jan 2020 => 2 (1st Sat in the year)
...
31 Dec 2020 => 53
1 Jan 2021 => 1
2 Jan 2021 => 2

If year end week shows 53 then again year start should shows 1. This should satisfy both the conditions.

Begin of a week by week number in SQL

Valerius Kaiser, December 06, 2021 - 12:34 pm UTC

If you have a NUMBER value p_YEAR, you can calculate the weekday of the 1 . January at first by the code
weekday := TO_CHAR(TO_DATE(TO_CHAR(p_YEAR) || '-01-01', 'YYYY-MM-DD', 'DAY', 'NLS_LANGUAGE = ENGLISH');
The first week of a year (by ISO stabdard) is the week with the first Thursday of the year in it. The following case command returns the begin of the first week of year:
CASE weekday
WHEN 'MONDAY' THEN TO_DATE(TO_CHAR(p_YEAR) || '-01-01', 'YYYY-MM-DD')
WHEN 'TUESDAY' THEN TO_DATE(TO_CHAR(p_YEAR-1) || '-12-31', 'YYYY-MM-DD')
WHEN 'WEDNESDAY' THEN TO_DATE(TO_CHAR(p_YEAR-1) || '-12-30', 'YYYY-MM-DD')
WHEN 'THURSDAY' THEN TO_DATE(TO_CHAR(p_YEAR-1) || '-12-29', 'YYYY-MM-DD')
WHEN 'FRIDAY' THEN TO_DATE(TO_CHAR(p_YEAR) || '-04-01', 'YYYY-MM-DD')
WHEN 'SATURDAY' THEN TO_DATE(TO_CHAR(p_YEAR) || '-03-01', 'YYYY-MM-DD')
WHEN 'SUNDAY' THEN TO_DATE(TO_CHAR(p_YEAR) || '-02-01', 'YYYY-MM-DD')
END;
You can define the function GET_BEGIN_FIRST_WEEK_ISO(p_YEAR NUMBER) which returns the value calculated by means of the code above. If K is the given week number then the value below returns the begin of the week with the number K in the given year p_YEAR:
GET_BEGIN_FIRST_WEEK_ISO(p_YEAR) +7*(K-1);

Chris Saxon
December 06, 2021 - 3:08 pm UTC

You could...

Though 4th Jan is always in the first week of the ISO year. So to get the first ISO week of the year I'd argue it's easier to find the start of the ISO week this date falls on:

with rws as (
  select 2015+level yr from dual
  connect by level <= 10
)
  select yr, 
         trunc ( 
           to_date ( yr || '-01-04', 'yyyy-mm-dd' ), 
           'iw' 
         ) dt
  from   rws;
  
YR      DT                     
   2016 04-JAN-2016 00:00:00    
   2017 02-JAN-2017 00:00:00    
   2018 01-JAN-2018 00:00:00    
   2019 31-DEC-2018 00:00:00    
   2020 30-DEC-2019 00:00:00    
   2021 04-JAN-2021 00:00:00    
   2022 03-JAN-2022 00:00:00    
   2023 02-JAN-2023 00:00:00    
   2024 01-JAN-2024 00:00:00    
   2025 30-DEC-2024 00:00:00