Examples of MariaDB DATEDIFF() Function
To understand the DATEDIFF() Function in MariaDB we need a table on which we will perform various operations and queries. So we have an employees table which consists of employee_id, employee_name, and join_date as Columns. The table is shown below.
employee_id |
employee_name |
join_date |
---|---|---|
1 |
John Doe |
2023-01-15 |
2 |
Jane Smith |
2022-05-20 |
3 |
Michael Johnson |
2024-02-10 |
4 |
Emily Davis |
2023-11-08 |
5 |
Alex Brown |
2022-09-03 |
Example 1: Calculate The Number of Days
Let’s calculate the number of days since each employee joined the company.
Query:
SELECT employee_name, DATEDIFF('2024-02-14', join_date) AS days_in_company FROM EmployeeJoinDates;
Output:
Explanation: The above query is used to select the employee names and the number of days they worked in the company, which is calculated as the difference between their join dates and ‘2024-02-14′. The function returns an absolute positive integer which is the number of days an employee worked in the company from their join date till ‘2024-02-14‘.
Example 2: Employeed Who Joined More Than 365 Days Ago
Let’s Find employees who joined more than 365 days ago in
Try any of the query, either use CURRENT_DATE() function or NOW() function, the result obtaines will be same.
Query:
SELECT employee_name FROM EmployeeJoinDates WHERE DATEDIFF(CURRENT_DATE(), join_date) > 365;
##OR
SELECT employee_name FROM EmployeeJoinDates WHERE DATEDIFF(NOW(), join_date) > 365;
Output:
Explanation: The above query is used for finding the employees who joined more than 365 days ago i.e. we just need to calculate the date difference ( between the employee join date and current date ) and retrieve the records with date difference more than 365.
Example 3: INVALID ARGUMENTS
Let’s see what happens if we use some string as a parameter or use only year or month as a parameter for the function.
Query:
SELECT DATEDIFF('2024-02-11', 'abc') AS DateDifference; #Non-Date Parameters
SELECT DATEDIFF('2024', '2023-11') AS DateDifference; #Missing parts
Output:
Explanation: The function takes only two parameters in the format ‘YYYYMMDD‘, so if there is only one argument or missing parts, the function returns NULL. Similarly, if the arguments are invalid strings like names or characters, the function returns NULL.
MariaDB DATEDIFF() Function
Sometimes, the difference between two dates in MariaDB is achieved through the DATEDIFF() function. The DATEDIFF() function offers a straightforward solution for measuring the gap between dates, whether it’s for scheduling tasks, tracking project durations, or analyzing trends over time. In this article, we’ll learn about the DATEDIFF() function by exploring its syntax and examples to master date manipulation in our MariaDB database.