So to find the number of weeks we divide by 7 and to have the first Monday be assigned the number 1, instead of 0, we need to add 1 to the result of dividing by 7.
#ORACLE SQL WEEK NUMBER ISO#
This formula finds the Monday of the ISO Week of dt and subtracts the first Monday of the year - using Oracle date arithmetic, where the difference between two dates is the number of days between them. So: to find the week number for any date dt, compute 1 + ( trunc(dt, 'iw') - trunc(date '', 'iw') ) / 7 Please check the Oracle documentation for "date literals" if you are not familiar with it. To input a fixed date, the best way is with the date literal syntax: date '' for January 7. (I won't explain that one - it's easy logic and it has nothing to do with programming.)
Then: To find the first Monday of the year, we can start with the date January 7 and ask for the Monday of the week in which January 7 falls. iw stands for ISO Week, standard week which starts on Monday and ends on Sunday. Notes: To find the Monday of the week for any given date dt, we can use trunc(dt, 'iw'). Other than that, the computation is trivial. The straightforward calculation below assigns to it week number 0. The week from January 2 to January 8 of 2017 is "week 1" according to your definition what week number do you assign to Sunday, January 1, 2017? Do you assign a week number for the prior calendar year? In a Comment I asked about January 1, 2017, as an example that was a Sunday. You (the OP) still have to explain what week number to assign to the first few days in a year, until the first Monday of the year.
0 kommentar(er)
