Skip to Main Content

Breadcrumb

Dev Live Dev Intro

We are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August and September. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Chris Saxon

Thanks for the question, Toufiq.

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

Answered by: Chris Saxon - Last updated: July 03, 2020 - 10:05 am UTC

Category: SQL*Plus - Version: 9i

Viewed 50K+ 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 we 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

and you rated our response

  (7 ratings)

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

Reviews

I have never understood the use case for WW

March 04, 2016 - 12:06 pm UTC

Reviewer: Kim Berg Hansen from Middelfart, Denmark

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

Followup  

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

October 18, 2017 - 6:56 pm UTC

Reviewer: Mike from New Mexico

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

May 14, 2019 - 9:55 pm UTC

Reviewer: A reader

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

Followup  

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

July 01, 2020 - 2:34 pm UTC

Reviewer: A reader from India

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

Query for getting fiscal week

July 02, 2020 - 11:27 am UTC

Reviewer: Vignesh from India

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

Followup  

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.

July 03, 2020 - 3:56 am UTC

Reviewer: Vignesh from India

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

Followup  

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.

July 03, 2020 - 11:06 am UTC

Reviewer: Vignesh from India

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.