## 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,
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.