List Remaining Days of the Week with SQL

Zahar Hilkevich

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 that 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 CONNECT BY 
   SIGN(TO_CHAR(SYSDATE + LEVEL - 1, 'D') -
        TO_CHAR(SYSDATE + LEVEL - 2, 'D')) =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:

https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/ROUND-and-TRUNC-Date-Functions.html#GUID-8E10AB76-21DA-490F-A389-023B648DDEF8

***

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/

Suggested Reading:

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”.

 

Leave a comment