How to Find Employees Who Joined in the Last 6 Months in SQL?

The problem

Given an employees table with a join_date column, return every employee who joined within the last 6 months.

Step 1: Create the table

CREATE TABLE employees (
  id          INT PRIMARY KEY,
  name        VARCHAR(50) NOT NULL,
  department  VARCHAR(50),
  salary      DECIMAL(10,2),
  join_date   DATE NOT NULL
);

The join_date column uses the DATE type, which stores a calendar date without a time. That keeps date comparisons simple and predictable.

Step 2: Insert sample data

INSERT INTO employees (id, name, department, salary, join_date) VALUES
(1,  'Asha',   'Engineering', 95000, '2024-03-15'),
(2,  'Ravi',   'Sales',       60000, '2025-11-20'),
(3,  'Meera',  'HR',          55000, '2026-05-10'),
(4,  'Kiran',  'Engineering', 88000, '2026-08-01'),
(5,  'Neha',   'Sales',       58000, '2026-09-15'),
(6,  'Rohan',  'Engineering', 91000, '2026-04-25'),
(7,  'Sana',   'HR',          52000, '2026-04-24'),
(8,  'Vikram', 'Sales',       62000, '2026-10-20'),
(9,  'Priya',  'Engineering', 97000, '2026-12-01'),
(10, 'Arjun',  'Sales',       59000, '2026-01-10');

Oracle does not accept multi-row VALUES, so use one INSERT per row there, with dates written as DATE '2026-04-25'.

Why this data?

The dates are built around a run date of 25 October 2026, which makes the 6-month cutoff 25 April 2026. A few rows are deliberately placed to test the boundaries:

• Rohan joined exactly on the cutoff date, so he should be included.
• Sana joined one day before the cutoff, so she should be excluded.
• Priya has a future join date of 1 December 2026, which many queries accidentally include.
• Asha, Ravi, and Arjun joined well before the cutoff and should be excluded.

Step 3: Write the query

The approach is the same in every database: compute a cutoff date of today minus 6 months, then keep rows where join_date is on or after it.

MySQL

SELECT *
FROM employees
WHERE join_date >= CURDATE() - INTERVAL 6 MONTH;

PostgreSQL

SELECT *
FROM employees
WHERE join_date >= CURRENT_DATE - INTERVAL '6 months';

SQL Server

SELECT *
FROM employees
WHERE join_date >= DATEADD(MONTH, -6, CAST(GETDATE() AS DATE));

Oracle

SELECT *
FROM employees
WHERE join_date >= ADD_MONTHS(TRUNC(SYSDATE), -6);

How it works

1. The database gets today's date and subtracts 6 months to produce the cutoff date.
2. join_date >= cutoff keeps only the employees who joined on or after that date.

Step 4: Check the result

Running the MySQL version on 25 October 2026 returns:

id	name	department	salary	join_date
3	Meera	HR	55000.00	2026-05-10
4	Kiran	Engineering	88000.00	2026-08-01
5	Neha	Sales	58000.00	2026-09-15
6	Rohan	Engineering	91000.00	2026-04-25
8	Vikram	Sales	62000.00	2026-10-20
9	Priya	Engineering	97000.00	2026-12-01

Rohan is correctly included because the comparison uses >=, and Sana is correctly excluded. But look at Priya: she hasn't joined yet, and she still appears. That brings us to the first pitfall.

Pitfall 1: Future dates slip through

The condition join_date >= cutoff has no upper limit, so any future join date also passes. If your table stores confirmed start dates for upcoming hires, add an upper bound.

SELECT *
FROM employees
WHERE join_date >= CURDATE() - INTERVAL 6 MONTH
  AND join_date <= CURDATE()
ORDER BY join_date DESC;

This returns five employees, newest first:

id	name	department	salary	join_date
8	Vikram	Sales	62000.00	2026-10-20
5	Neha	Sales	58000.00	2026-09-15
4	Kiran	Engineering	88000.00	2026-08-01
3	Meera	HR	55000.00	2026-05-10
6	Rohan	Engineering	91000.00	2026-04-25

Pitfall 2: Dates versus timestamps

If join_date also stores a time of day, compare it carefully. Using a date-only function for today, such as CURDATE() or CURRENT_DATE, makes the cutoff start at midnight, so someone who joined at 9 a.m. on the cutoff day is still included. Mixing a date column with a full timestamp such as NOW() can quietly exclude people on the boundary day.

Pitfall 3: Rolling window versus calendar window

The queries above use a rolling 6 months counted back from today. If your business means "since the start of the month 6 months ago," truncate the cutoff to the first of that month instead. In PostgreSQL, for example:

WHERE join_date >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '6 months')

Agree on the definition with whoever asked for the report, because the two versions give different answers.

Pitfall 4: Don't wrap the column in a function

A query such as WHERE DATEDIFF(CURDATE(), join_date) <= 180 applies a function to every row's join_date, which prevents the database from using an index on that column. Keep the column bare and do the date math on the other side of the comparison, as in the queries above. This also avoids approximating 6 months as 180 days.

Testing on any day: use a fixed date

CURDATE() depends on the day you run the query, so your results will shift over time. To reproduce the tables in this post on any day, replace it with fixed dates.

SELECT *
FROM employees
WHERE join_date >= DATE '2026-04-25'
  AND join_date <= DATE '2026-10-25'
ORDER BY join_date DESC;

Fixed dates are also handy for writing repeatable tests and for debugging a report that "looked different yesterday."

Quick reference

Database	Cutoff expression
MySQL	CURDATE() - INTERVAL 6 MONTH
PostgreSQL	CURRENT_DATE - INTERVAL '6 months'
SQL Server	DATEADD(MONTH, -6, CAST(GETDATE() AS DATE))
Oracle	ADD_MONTHS(TRUNC(SYSDATE), -6)

Final thoughts

The core of this query is a single comparison, join_date >= cutoff, but a trustworthy version needs more care: use >= so the boundary day counts, add an upper bound to keep future hires out, keep the column free of functions, and decide whether you mean a rolling or calendar window. Build your sample data around those edge cases, as we did here, and you will catch the mistakes before they reach a report.