Oracle SQL – Date Range for ISO Week
Recently I had to work on a report for the marketing team.
This report requires me to group the transactions by ISO week, and for the very first time, i’m glad to be working on Oracle Database.
In the report, I’ll need to display the date range for all the weeks, so after googling for some time, i came across this query.
SELECT YEAR, WEEK, NEXT_DAY( TO_DATE( '04-JAN' || YEAR, 'DD-MON-YYYY' ) + (WEEK-2)*7, 'MON' ) FROM (SELECT '2003' YEAR, ROWNUM WEEK FROM ALL_OBJECTS WHERE ROWNUM <= 53)
which will return the results
YEAR WEEK NEXT_DAY(TO_DATE('04-JAN-'||YEAR,'DD-MON-YYYY')+(WEEK-2)*7,'MON')
---- ---------------------- -------------------------
2003 1 30-DEC-02
2003 2 06-JAN-03
2003 3 13-JAN-03
2003 4 20-JAN-03
2003 5 27-JAN-03
2003 6 03-FEB-03
2003 7 10-FEB-03
2003 8 17-FEB-03
2003 9 24-FEB-03
2003 10 03-MAR-03
2003 11 10-MAR-03
2003 12 17-MAR-03
2003 13 24-MAR-03
2003 14 31-MAR-03
2003 15 07-APR-03
2003 16 14-APR-03
2003 17 21-APR-03
2003 18 28-APR-03
2003 19 05-MAY-03
2003 20 12-MAY-03
2003 21 19-MAY-03
2003 22 26-MAY-03
2003 23 02-JUN-03
2003 24 09-JUN-03
2003 25 16-JUN-03
2003 26 23-JUN-03
2003 27 30-JUN-03
2003 28 07-JUL-03
2003 29 14-JUL-03
2003 30 21-JUL-03
2003 31 28-JUL-03
2003 32 04-AUG-03
2003 33 11-AUG-03
2003 34 18-AUG-03
2003 35 25-AUG-03
2003 36 01-SEP-03
2003 37 08-SEP-03
2003 38 15-SEP-03
2003 39 22-SEP-03
2003 40 29-SEP-03
2003 41 06-OCT-03
2003 42 13-OCT-03
2003 43 20-OCT-03
2003 44 27-OCT-03
2003 45 03-NOV-03
2003 46 10-NOV-03
2003 47 17-NOV-03
2003 48 24-NOV-03
2003 49 01-DEC-03
2003 50 08-DEC-03
2003 51 15-DEC-03
2003 52 22-DEC-03
2003 53 29-DEC-03
53 rows selected
But unfortunately, there is a slight flaw. 29-Dec-2003 is not a week 53 of 2003 but instead is week 01 of 2004.
Therefore I updated the query to convert the start_date using TO_CHAR function with the format ‘IW’ for ISO Week and IYYY for ISO Year.
SELECT TO_CHAR(START_DATE,'IYYY') YEAR, TO_CHAR(START_DATE,'IW') WEEK, START_DATE, START_DATE+6 END_DATE FROM (SELECT NEXT_DAY( TO_DATE( '04-JAN-' || YEAR, 'DD-MON-YYYY' ) + (WEEK-2)*7, 'MON' ) START_DATE FROM (SELECT '2003' YEAR, ROWNUM WEEK FROM ALL_OBJECTS WHERE ROWNUM <= 53))
The results return now shows the correct ISO week for 29-Dec-2003 to be in Week 1 of 2004.
Results:
YEAR WEEK START_DATE END_DATE ---- ---- ------------------------- ------------------------- 2003 01 30-DEC-02 05-JAN-03 2003 02 06-JAN-03 12-JAN-03 2003 03 13-JAN-03 19-JAN-03 2003 04 20-JAN-03 26-JAN-03 2003 05 27-JAN-03 02-FEB-03 2003 06 03-FEB-03 09-FEB-03 2003 07 10-FEB-03 16-FEB-03 2003 08 17-FEB-03 23-FEB-03 2003 09 24-FEB-03 02-MAR-03 2003 10 03-MAR-03 09-MAR-03 2003 11 10-MAR-03 16-MAR-03 2003 12 17-MAR-03 23-MAR-03 2003 13 24-MAR-03 30-MAR-03 2003 14 31-MAR-03 06-APR-03 2003 15 07-APR-03 13-APR-03 2003 16 14-APR-03 20-APR-03 2003 17 21-APR-03 27-APR-03 2003 18 28-APR-03 04-MAY-03 2003 19 05-MAY-03 11-MAY-03 2003 20 12-MAY-03 18-MAY-03 2003 21 19-MAY-03 25-MAY-03 2003 22 26-MAY-03 01-JUN-03 2003 23 02-JUN-03 08-JUN-03 2003 24 09-JUN-03 15-JUN-03 2003 25 16-JUN-03 22-JUN-03 2003 26 23-JUN-03 29-JUN-03 2003 27 30-JUN-03 06-JUL-03 2003 28 07-JUL-03 13-JUL-03 2003 29 14-JUL-03 20-JUL-03 2003 30 21-JUL-03 27-JUL-03 2003 31 28-JUL-03 03-AUG-03 2003 32 04-AUG-03 10-AUG-03 2003 33 11-AUG-03 17-AUG-03 2003 34 18-AUG-03 24-AUG-03 2003 35 25-AUG-03 31-AUG-03 2003 36 01-SEP-03 07-SEP-03 2003 37 08-SEP-03 14-SEP-03 2003 38 15-SEP-03 21-SEP-03 2003 39 22-SEP-03 28-SEP-03 2003 40 29-SEP-03 05-OCT-03 2003 41 06-OCT-03 12-OCT-03 2003 42 13-OCT-03 19-OCT-03 2003 43 20-OCT-03 26-OCT-03 2003 44 27-OCT-03 02-NOV-03 2003 45 03-NOV-03 09-NOV-03 2003 46 10-NOV-03 16-NOV-03 2003 47 17-NOV-03 23-NOV-03 2003 48 24-NOV-03 30-NOV-03 2003 49 01-DEC-03 07-DEC-03 2003 50 08-DEC-03 14-DEC-03 2003 51 15-DEC-03 21-DEC-03 2003 52 22-DEC-03 28-DEC-03 2004 01 29-DEC-03 04-JAN-04 53 rows selected
I don’t know what to title this post
Im running out of brain power as of now.
This morning received a call from my colleague telling me that one of the server died. So I had to think of an alternative server that can be temporary used to tide over the event.
Took me almost a day to finish setting up the applications and finally it’s up and running stable.
So another issue was my reports generation failed again. Due to the late settlement file from shittybank without prior notifications.
Had to clear the backlog and try to rerun again which I had to OT till 9pm to generate it out for the finance.
Right now I’m lying in the cold server room floor trying to take a break while my DBA are doing their Oracle patching for the upcoming IT audit.
I’m here just to stop the application and subsequently start the application and make sure everything runs fine.
After they are done, I will have to manually generate the reports for today and checked it before I can head home to rest.
I’m sure I won’t be to rest in the day time as 2 of my colleagues are on leave and I’m covering for them 
Enough blogging. Need to try and get some serious sleep in between the server racks. Got a game of squash in the evening xD
What a way to kick start my week. xD
Filehippo