SQL Puzzle of the day:
List all days from today till the last day of the week. The query should work regardless of the regional settings that affect first day of the week, whether it is Sunday, Monday, or any other day.
The trick here is not to attempt figuring out the current day of the week, whether it is Friday, Saturday or anything else. We need to apply a date function and returns the same value (or is constantly staying in the same interval) for all the days within the same week. The following 3 strategies are all based on such functions: TRUNC and TO_CHAR.
Strategy #1: Using TRUNC with ‘D’ format
SELECT SYSDATE + LEVEL - 1 AS Day FROM dual CONNECT BY TRUNC(SYSDATE, 'D') = TRUNC(SYSDATE + LEVEL - 1, 'D')
Strategy #2: Using SIGN and TO_CHAR with ‘D’ format
SELECT SYSDATE + LEVEL - 1 AS Day FROM dual CONNECT BY SIGN(TO_CHAR(SYSDATE, 'D')-1)=1
Strategy #3: Using TRUNC and calculating the week end by adding 7 to the first day
SELECT SYSDATE + LEVEL - 1 AS Day FROM dual CONNECT BY TRUNC(SYSDATE+LEVEL)-TRUNC(SYSDATE,'D')<=7
Here is a useful link to Oracle documentation that explains different format strings:
If you find this post useful, please press the LIKE button and subscribe.
My Oracle Group on Facebook:
Also, you may want to join my Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/
Would you like to read about many more tricks and puzzles? For more clever tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds”.