List all weekend days for the current month

Problem: List all weekend days for the current month

Problem Level: Beginner/Intermediate

Solution:

We will break down the problem into 2 parts:
1) Get all days for the current month
2) Selecting only weekends

1) Get all days for the current month

Selecting all days from the current months requires one of two things:

  • Knowing the first day of the month
  • Knowing how to check for the end of the month or how to get the number of days in the month

The first task is fairly simple. We can use TRUNC function to get the first day of the month as follows:

SELECT TRUNC(SYSDATE, 'MONTH') first_day
FROM DUAL

End of the month can be found by adding one month and subtracting one day:

SELECT TRUNC(SYSDATE, 'MONTH') first_day,
       ADD_MONTHS(TRUNC(SYSDATE, 'MONTH'), 1)-1 last_day
FROM dual

Now, if you know how to generate a numeric range, you can also generate the date range as follows:

SELECT TRUNC(SYSDATE, 'MONTH') + LEVEL - 1 as "day"
FROM dual
CONNECT BY TRUNC(SYSDATE, 'MONTH')+LEVEL<=ADD_MONTHS(TRUNC(SYSDATE, 'MONTH'), 1)

Alternatively, you can control the end of the month by checking if the “current level’s day” has the same month:

SELECT TRUNC(SYSDATE, 'MONTH') + LEVEL - 1 as "day"
FROM dual
CONNECT BY TRUNC(TRUNC(SYSDATE, 'MONTH')+LEVEL-1, 'MONTH')=TRUNC(SYSDATE, 'MONTH') 

2) Selecting only weekends

In the following post I have altready demonstrated how to reliably get the first and last week days (i.e. weekend), so applying the method presenting in that post to all days in the current month should accomplish our goal:

Week_Start and Week_End custom functions in Oracle

WITH days AS (
SELECT TRUNC(SYSDATE, 'MONTH')+LEVEL-1 AS "day"
FROM dual
CONNECT BY TRUNC(TRUNC(SYSDATE, 'MONTH')+LEVEL-1, 'MONTH')=TRUNC(SYSDATE, 'MONTH')
)
SELECT "day", TO_CHAR("day", 'Day') "day of the week"
FROM days
WHERE "day" IN (TRUNC("day", 'DAY'), TRUNC("day", 'DAY')+6)

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s