- Aggregate functions
- ALTER TABLE statement
- AVERAGE function
- BETWEEN operator
- CASE expression
- CAST() function
- COALESCE() function
- Comment
- Common table expression
- CONCAT() function
- Constraints
- CONTAINS
- CONVERT function
- COUNT() function
- CREATE TABLE statement
- CROSS JOIN
- Cursor
- Data types
- Date functions
- DATEADD() function
- DATEDIFF() function
- DELETE statement
- DROP TABLE statement
- EXISTS operator
- FORMAT() function
- GROUP BY statement
- HAVING clause
- IF statement
- Index
- Injection
- INNER JOIN
- INSERT INTO statement
- IS NOT NULL condition
- IS NULL condition
- Joins
- LAG function
- LEFT JOIN
- LENGTH() function
- LIKE operator
- LIMIT clause
- MERGE statement
- Normalization
- Not equal
- Operators
- ORDER BY clause
- Partition
- Pivot table
- Regex
- REPLACE function
- ROUND function
- SELECT DISTINCT clause
- SELECT statement
- Set operators
- Stored procedure
- String functions
- Subquery
- Substring
- Temporary table
- Transaction
- Trigger
- TRUNCATE TABLE
- UPDATE statement
- Views
- WHERE clause
- Window functions
SQL
SQL DATEDIFF() Function: Syntax, Usage, and Examples
The SQL DATEDIFF
function calculates the difference between two dates and returns the result as an integer. It’s commonly used to determine how many days, months, or years have passed between two time values. DATEDIFF SQL
is widely supported, especially in SQL Server, MySQL (via TIMESTAMPDIFF), and other Transact-SQL-based systems.
How to Use DATEDIFF in SQL
The general syntax of DATEDIFF SQL
depends on the database, but in SQL Server and Transact-SQL, the syntax looks like this:
DATEDIFF(datepart, start_date, end_date)
datepart
: The unit of time you want the difference in (day, month, year, etc.)start_date
: The beginning of the periodend_date
: The end of the period
Basic Example
SELECT DATEDIFF(day, '2023-01-01', '2023-01-10') AS difference;
-- Output: 9
This shows that there are 9 full days between the two dates.
When to Use SQL DATEDIFF
SQL DATEDIFF
is useful in scenarios like:
- Calculating age from a birth date
- Finding overdue tasks
- Measuring time between transactions
- Generating reports with durations
- Filtering data by elapsed time
Typical Use Cases
Age Calculation
SELECT DATEDIFF(year, birth_date, GETDATE()) AS age
FROM employees;
This calculates each employee’s age in full years.
Order Fulfillment Time
SELECT order_id, DATEDIFF(day, order_date, delivery_date) AS days_to_deliver
FROM orders;
This is helpful for logistics tracking and performance reviews.
Time-Sensitive Alerts
SELECT task_id
FROM tasks
WHERE DATEDIFF(day, due_date, GETDATE()) > 0;
Returns all tasks past their due date.
Examples of DATEDIFF in SQL
Example 1: Difference in Days
SELECT DATEDIFF(day, '2023-04-01', '2023-04-10') AS days_apart;
-- Output: 9
This calculates the number of days between two fixed dates.
Example 2: Difference in Months
SELECT DATEDIFF(month, '2022-10-01', '2023-04-01') AS months_apart;
-- Output: 6
This shows that 6 full months passed between the dates.
Example 3: Using GETDATE() for Current Time
SELECT name, DATEDIFF(day, signup_date, GETDATE()) AS days_since_signup
FROM users;
This gives you a running counter of how long each user has been registered.
Example 4: datediff function in SQL Server with Time
You can also calculate time differences in hours, minutes, or seconds:
SELECT DATEDIFF(hour, '2023-04-01 08:00:00', '2023-04-01 14:00:00') AS hours_passed;
-- Output: 6
This is useful for server logs, uptime tracking, or shift management.
Learn More About DATEDIFF SQL
DATEDIFF in SQL for Filtering
You can filter records based on a dynamic time window:
SELECT *
FROM logins
WHERE DATEDIFF(day, login_time, GETDATE()) <= 7;
This returns users who logged in during the past week.
Or filter by future dates:
SELECT *
FROM subscriptions
WHERE DATEDIFF(day, GETDATE(), expiration_date) <= 30;
Shows subscriptions expiring in the next 30 days.
DATEDIFF and Negative Values
DATEDIFF SQL
can return negative numbers if the start date is after the end date:
SELECT DATEDIFF(day, '2023-04-10', '2023-04-01') AS diff;
-- Output: -9
This is useful for checking how many days remain until a deadline.
DATEDIFF Transact SQL Specifics
In Transact-SQL (used in SQL Server), datediff transact sql
supports a variety of datepart
values:
year
quarter
month
day
hour
minute
second
millisecond
microsecond
nanosecond
Example with minutes:
SELECT DATEDIFF(minute, '2023-04-01 10:00', '2023-04-01 10:45') AS duration;
-- Output: 45
DATEDIFF and Zero Output
If both dates fall within the same time unit (e.g., the same day), and you're counting by a larger unit (like day
or month
), the result may be zero:
SELECT DATEDIFF(day, '2023-04-01 01:00', '2023-04-01 23:59') AS diff;
-- Output: 0
This is because DATEDIFF
counts the number of boundaries crossed, not the exact difference.
Using DATEDIFF with Aliases and CASE
You can build conditional queries using DATEDIFF
:
SELECT task_id,
CASE
WHEN DATEDIFF(day, due_date, GETDATE()) > 0 THEN 'Overdue'
ELSE 'On Time'
END AS status
FROM tasks;
This tags each task with a status based on how far past its due date it is.
Nesting DATEDIFF Inside Other Functions
You can wrap DATEDIFF
inside other functions like ABS()
to get absolute differences:
SELECT ABS(DATEDIFF(day, hire_date, termination_date)) AS total_days;
Or use it in ORDER BY
to sort by how recent or old something is:
SELECT name, DATEDIFF(day, created_at, GETDATE()) AS age
FROM products
ORDER BY age DESC;
DATEDIFF vs TIMESTAMPDIFF
Some databases like MySQL don’t use DATEDIFF
in the same way. Instead, they use TIMESTAMPDIFF()
:
SELECT TIMESTAMPDIFF(DAY, '2023-01-01', '2023-01-10') AS diff;
This acts similarly to sql datediff
in SQL Server. Always check your database’s specific implementation.
Timezones and DATEDIFF
If your application deals with different time zones, remember that DATEDIFF
does not automatically adjust for time zone offsets. You’ll need to handle time zone conversions explicitly before comparing times.
The SQL DATEDIFF
function is a simple yet powerful tool for measuring time-based differences in your data. From tracking user activity to calculating contract durations or overdue tasks, it helps developers and analysts work efficiently with time-sensitive information. Mastering datediff in sql
allows you to write cleaner queries, build smarter alerts, and drive insights across any date-based data set.
Sign up or download Mimo from the App Store or Google Play to enhance your programming skills and prepare for a career in tech.