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
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'
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
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.
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);
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