Mastering SQL DATE Functions

Dates and times are an essential part of almost every database application. Whether you're working with transactions, event logging, or scheduling, you will frequently need to manipulate and query date and time data. SQL provides a rich set of functions to handle DATE and TIME values, allowing you to perform operations like adding or subtracting days, extracting specific parts of a date, and formatting dates to match your requirements.

1. Introduction to SQL DATE Types

Before diving into the functions, let's first understand the SQL date types. SQL typically uses the following types to store date and time information:

• DATE: Stores only the date in the format YYYY-MM-DD (e.g., 2025-01-14).

• TIME: Stores only the time in the format HH:MM:SS (e.g., 12:30:45).

• DATETIME: Stores both the date and time in the format YYYY-MM-DD HH:MM:SS (e.g., 2025-01-14 12:30:45).

• TIMESTAMP: Stores both date and time but is more closely related to the system's time zone.

• YEAR: Stores a year value in the format YYYY (e.g., 2025).

SQL Date functions work on these types, and most SQL databases (like MySQL, PostgreSQL, SQL Server) have similar functions for working with dates.

---

2. Common SQL DATE Functions

Let’s explore the most common SQL DATE functions with detailed explanations and examples.

2.1 CURDATE() and CURRENT_DATE

The CURDATE() function returns the current date based on the server's system time.

SELECT CURDATE();  -- Output: 2025-01-14

• CURRENT_DATE is an alias for CURDATE(). They return the same result.

SELECT CURRENT_DATE;  -- Output: 2025-01-14

2.2 NOW() and CURRENT_TIMESTAMP

The NOW() function returns the current date and time.

SELECT NOW();  -- Output: 2025-01-14 12:30:45

• CURRENT_TIMESTAMP is an alias for NOW(). It returns the same result.

SELECT CURRENT_TIMESTAMP;  -- Output: 2025-01-14 12:30:45

2.3 DATE()

The DATE() function extracts only the date part from a DATETIME or TIMESTAMP value.

SELECT DATE('2025-01-14 12:30:45');  -- Output: 2025-01-14

This is useful when you want to ignore the time component.

2.4 DATE_ADD() and DATE_SUB()

These functions allow you to add or subtract a specific interval (such as days, months, or years) from a date.

• DATE_ADD(date, INTERVAL value unit): Adds a specified value to a date.

• DATE_SUB(date, INTERVAL value unit): Subtracts a specified value from a date.

Example: Adding 10 days to a date.

SELECT DATE_ADD('2025-01-14', INTERVAL 10 DAY);  -- Output: 2025-01-24

Example: Subtracting 2 months from a date.

SELECT DATE_SUB('2025-01-14', INTERVAL 2 MONTH);  -- Output: 2024-11-14

2.5 DATEDIFF()

The DATEDIFF() function calculates the number of days between two dates.

SELECT DATEDIFF('2025-01-14', '2025-01-01');  -- Output: 13

2.6 DATE_FORMAT()

The DATE_FORMAT() function allows you to format a DATE, DATETIME, or TIMESTAMP value into a specific string format.

SELECT DATE_FORMAT('2025-01-14', '%W, %M %d, %Y');  -- Output: Tuesday, January 14, 2025

The format specifiers used here include:

• %W - Weekday name

• %M - Month name

• %d - Day of the month (01-31)

• %Y - Four-digit year

2.7 YEAR(), MONTH(), and DAY()

These functions extract specific parts (year, month, or day) from a date.

SELECT YEAR('2025-01-14');  -- Output: 2025
SELECT MONTH('2025-01-14');  -- Output: 1
SELECT DAY('2025-01-14');  -- Output: 14

2.8 DAYNAME(), DAYOFWEEK()

These functions return information about the day of the week.

SELECT DAYNAME('2025-01-14');  -- Output: Tuesday
SELECT DAYOFWEEK('2025-01-14');  -- Output: 3 (1 = Sunday, 7 = Saturday)

2.9 LAST_DAY()

The LAST_DAY() function returns the last day of the month for a given date.

SELECT LAST_DAY('2025-01-14');  -- Output: 2025-01-31

2.10 EXTRACT()

The EXTRACT() function extracts a specific part (such as year, month, or day) from a date or timestamp.

SELECT EXTRACT(YEAR FROM '2025-01-14');  -- Output: 2025
SELECT EXTRACT(MONTH FROM '2025-01-14');  -- Output: 1
SELECT EXTRACT(DAY FROM '2025-01-14');  -- Output: 14

---

3. Manipulating Date and Time Values

SQL allows for more advanced manipulation of date and time values. Here are a few common use cases:

• Getting the current date and adding a day to it:

SELECT CURDATE(), DATE_ADD(CURDATE(), INTERVAL 1 DAY);  -- Output: 2025-01-14, 2025-01-15

• Subtracting a specific number of days from the current date:

SELECT DATE_SUB(CURDATE(), INTERVAL 7 DAY);  -- Output: 2025-01-07

• Getting the first day of the month:

SELECT DATE_FORMAT(NOW(), '%Y-%m-01');  -- Output: 2025-01-01

---

4. SQL DATE Arithmetic

SQL DATE arithmetic allows you to perform operations on dates. Here are some practical examples:

• Find the number of days between two dates:

SELECT DATEDIFF('2025-01-14', '2024-12-25');  -- Output: 20

• Add a certain number of years to a date:

SELECT DATE_ADD('2025-01-14', INTERVAL 2 YEAR);  -- Output: 2027-01-14

---

5. SQL DATE Formatting

Formatting dates in SQL is crucial when you need to present data in a user-friendly way or store it in a specific format.

For example, to format a date as DD/MM/YYYY:

SELECT DATE_FORMAT('2025-01-14', '%d/%m/%Y');  -- Output: 14/01/2025

---

6. SQL DATE Functions in Practice: Examples

Let’s put all of these functions together in a practical example.

Scenario: A company needs to track employee attendance. You need to:

• Calculate how many days an employee has worked since their first login.

• Extract the year and month for monthly reports.

• Get the last working day of the month.

-- 1. Calculate days worked since the first login
SELECT employee_id, DATEDIFF(CURDATE(), MIN(login_date)) AS days_worked
FROM employee_attendance
GROUP BY employee_id;

-- 2. Extract the year and month from the login date
SELECT employee_id, YEAR(login_date) AS year, MONTH(login_date) AS month
FROM employee_attendance;

-- 3. Get the last working day of the month
SELECT employee_id, LAST_DAY(login_date) AS last_working_day
FROM employee_attendance;

7. SQL Date Function Interview Questions

5 SQL interview questions based on DATE functions that require a more conceptual understanding and problem-solving approach. These questions are designed to test your knowledge and ability to apply date functions in real-world scenarios:

7.1 Calculate the Age of Employees Based on Their Date of Birth

Problem: Given a table employees with the columns employee_id, name, and dob (Date of Birth), calculate the age of each employee as of today. Ensure the result is rounded down to the nearest integer. The dob column contains the employee's birthdate.

SQL Query:

SELECT employee_id, name, 
       FLOOR(DATEDIFF(CURDATE(), dob) / 365) AS age
FROM employees;

Explanation:

• The DATEDIFF() function calculates the number of days between today (CURDATE()) and the employee's date of birth.

• We divide the result by 365 to convert it into years and use FLOOR() to round down the age.

---

7.2 Find the Employees Who Have Worked for More Than 5 Years

Problem: From the employees table, write a query to find all employees who have been working for more than 5 years. The table has the columns employee_id, name, and hire_date. Consider the current date for the calculation.

SQL Query:

SELECT employee_id, name
FROM employees
WHERE DATEDIFF(CURDATE(), hire_date) > 5 * 365;

Explanation:

• The DATEDIFF() function returns the number of days between the current date and the hire_date.

• We check if the difference in days is greater than 5 years (approximately 5 * 365 days).

Note: A more accurate approach could consider leap years, but this approach provides a simple approximation.

---

7.3 Get the First and Last Day of the Current Month

Problem: Write a SQL query to find the first and last day of the current month for a given date, say 2025-01-14. Assume the date is in the current_date column of the table.

SQL Query:

SELECT current_date,
       DATE_FORMAT(current_date, '%Y-%m-01') AS first_day_of_month,
       LAST_DAY(current_date) AS last_day_of_month;

Explanation:

• DATE_FORMAT(current_date, '%Y-%m-01') gives the first day of the month by resetting the day part to 01.

• LAST_DAY(current_date) returns the last day of the month.

---

7.4 Find the Next Working Day After a Given Date

Problem: Write a query to find the next working day (Monday to Friday) after a given date. Assume that the given_date column in the table stores the date from which we want to calculate the next working day.

SQL Query:

SELECT given_date,
       CASE 
           WHEN DAYOFWEEK(given_date) = 6 THEN DATE_ADD(given_date, INTERVAL 2 DAY)  -- Saturday
           WHEN DAYOFWEEK(given_date) = 7 THEN DATE_ADD(given_date, INTERVAL 1 DAY)  -- Sunday
           ELSE DATE_ADD(given_date, INTERVAL 1 DAY)  -- Weekdays
       END AS next_working_day
FROM your_table;

Explanation:

• DAYOFWEEK() returns the weekday number (1 = Sunday, 7 = Saturday).

• Based on the weekday, we use DATE_ADD() to calculate the next working day.

---

7.5 Calculate the Number of Days Between Two Dates Excluding Weekends

Problem: Write a query to calculate the number of working days (Monday to Friday) between two dates in a table, orders, with columns order_id, start_date, and end_date. The start_date and end_date are stored in DATE format.

SQL Query:

SELECT order_id,
       DATEDIFF(end_date, start_date) - 
       (2 * (DATEDIFF(end_date, start_date) / 7)) - 
       CASE 
           WHEN DAYOFWEEK(start_date) = 1 THEN 1
           WHEN DAYOFWEEK(end_date) = 7 THEN 1
           ELSE 0
       END AS working_days
FROM orders;

Explanation:

• The DATEDIFF() function calculates the total number of days between start_date and end_date.

• We subtract weekends by checking how many full weeks fall between the two dates (dividing by 7 and multiplying by 2 for weekends).

• The CASE statement ensures we handle partial weekends (if the start_date is Sunday or the end_date is Saturday).

SQL date functions are powerful tools for manipulating and analyzing time-based data. From calculating the difference between dates to extracting specific parts like year or month, these functions are essential for handling real-world data scenarios. By practicing these examples, you'll be well-prepared for both practical tasks and SQL interviews. Keep honing your skills, and you'll be proficient in no time!

Happy querying!