ORACLE SQL WEEK NUMBER ISO
Add 3 days and you have Thursday of the first ISO week and then.Truncate it back to Monday to be the start of the ISO week.Start with the 4th January of your year.+ (to_number(substr(your_column, instr(your_column, '_', -1, 1) + 1)) * 7)ĭbfiddle showing the intermediate steps so you can see what's happening.Ĭonverting to an ISO week is relatively simple as the 4th of January will always be in the first ISO week of the year.
Then convert the year number to the first day of the ISO year: trunc(to_date(substr(your_column, instr(your_column, '_', -1, 2) + 1, 4), 'YYYY'), 'IYYY')Īnd either add the number of weeks - 1 * 7 days to get the Monday of that week, then add 3 for the Thursday or add the number of weeks * 7 days to get the Monday of the following week and subtract four for the Thursday of the week you want: trunc(to_date(substr(your_column, instr(your_column, '_', -1, 2) + 1, 4), 'YYYY'), 'IYYY') So: to find the week number for any date dt, compute 1 + ( trunc (dt, 'iw') - trunc (date '', 'iw') ) / 7 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. The second subquery takes that list and generates the U.S. The first subquery shown generates a list of dates starting with January 1 of the current year and ends 365 days later. week numbering system is WW, in either upper or lower case. SQL> ed Wrote file afiedt. In Oracle syntax the format specifier for the U.S. You can extract the year and week numbers separately: substr(your_column, instr(your_column, '_', -1, 2) + 1, 4) As Andrew said, this is a nlsterritory definition, so you can change nlsterritory to change the first day of week : SQL> alter session set nlsterritory 'FRANCE ' Session altered.
0 kommentar(er)
