How can you write an SQL query to find employees who joined within the last 6 months? Assume you have an employees table with columns employee_id, employee_name, and joining_date.

Answer:

To find employees who joined within the last 6 months, we can use the DATEADD() or INTERVAL function (depending on the SQL database) to calculate a date range. Here’s how to achieve this in SQL.

Solution: Using DATEADD or INTERVAL

Assume the employees table structure is as follows:

We want to find employees whose joining_date is within the last 6 months from the current date.

SQL Query (using DATEADD in SQL Server):

SQL Query (using INTERVAL in MySQL):

Explanation:

1. DATEADD or DATE_SUB Function: These functions allow us to subtract 6 months from the current date (GETDATE() in SQL Server or CURDATE() in MySQL).
2. Filtering Condition: The WHERE joining_date >= ... clause filters only employees who joined on or after the calculated date, which is 6 months before today’s date.

Expected Output:

Based on the example data, if today’s date is assumed to be 2024-11-01, the result would look like:

Solution for Databases without DATEADD or DATE_SUB

In databases that don’t support DATEADD or DATE_SUB, we can calculate the past date manually using the year and month functions.

For instance, if the database allows date arithmetic: