Your HR manager asks you to find all employees who joined during the current month.
You could filter for November 2026, but would you have to change the query when December starts?
Can you make it dynamic?
employees
emp_id|employee_name|joining_date
101 | Amit | 2026-11-02
102 | Neha | 2026-10-25
103 | Rahul | 2026-11-05
104 | Priya | 2026-09-18
105 | Sneha | 2026-11-08
106 | Karan | 2026-12-01
Your Challenge
Write a query to:
- Find employees who joined this month.
- Exclude previous and future months.
- Avoid hardcoding dates.
Think before you scroll!
The SQL Solution (Oracle)
SELECT emp_id, employee_name, joining_date
FROM employees
WHERE joining_date >= TRUNC(SYSDATE, 'MM')
AND joining_date < ADD_MONTHS(TRUNC(SYSDATE, 'MM'), 1);
Expected Output
emp_id|employee_name|joining_date
101 | Amit | 2026-11-02
103 | Rahul | 2026-11-05
105 | Sneha | 2026-11-08
Why Does This Work?
"TRUNC(SYSDATE, 'MM')" returns the first day of the current month.
"ADD_MONTHS(..., 1)" returns the first day of the next month.
So the query retrieves everything from the beginning of this month up to, but not including, the next month.
One important point:
Using BETWEEN with the first day of the next month as the ending date can cause timestamp-related issues.
This pattern is safer:
date_column >= start_date
AND date_column < next_period_start
You can use it for daily and monthly reports too.
Learn More
Explore SQL date functions like TRUNC(), EXTRACT() and TO_CHAR():
Your Turn
How would you find employees who joined during the previous month?
Share your query in the comments!
Top comments (1)
For the previous month I'd shift the same half-open range back one month, keeping joining_date bare so an index on it can still be used:
WHERE joining_date >= ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -1)
AND joining_date < TRUNC(SYSDATE, 'MM');
That way the 1st of last month is included, and anything timestamped on the 1st of this month stays out.