SQL Server DATEDIFF()

SQL Server DATEDIFF() Function

The SQL Server DATEDIFF() function returns the difference between two dates, based on a specified date part. This function is useful for calculating the difference between dates in various units such as days, months, or years.

Syntax

SELECT DATEDIFF(datepart, startdate, enddate);

The DATEDIFF() function takes three arguments:

  • datepart: The part of the date to calculate the difference. Common values include year, quarter, month, day, week, hour, minute, second, and millisecond.
  • startdate: The starting date for the calculation.
  • enddate: The ending date for the calculation.

Example SQL Server DATEDIFF() Function Queries

Let's look at some examples of SQL Server DATEDIFF() function queries:

1. Difference in Days

SELECT DATEDIFF(day, '2024-06-01', '2024-06-15') AS date_difference;

This query returns the difference in days between '2024-06-01' and '2024-06-15'. The result will be:

date_difference
---------------
14

2. Difference in Months

SELECT DATEDIFF(month, '2024-01-01', '2024-06-01') AS date_difference;

This query returns the difference in months between '2024-01-01' and '2024-06-01'. The result will be:

date_difference
---------------
5

3. Difference in Years

SELECT DATEDIFF(year, '2020-01-01', '2024-01-01') AS date_difference;

This query returns the difference in years between '2020-01-01' and '2024-01-01'. The result will be:

date_difference
---------------
4

4. DATEDIFF() with a Column

SELECT order_date, DATEDIFF(day, order_date, GETDATE()) AS days_since_order
FROM orders;

This query calculates the difference in days between the order_date column and the current date for each record in the orders table. The result will show the original order_date and the corresponding days_since_order.

5. DATEDIFF() with a Variable

DECLARE @start_date DATETIME, @end_date DATETIME;
SET @start_date = '2024-01-01';
SET @end_date = '2024-06-01';
SELECT DATEDIFF(month, @start_date, @end_date) AS date_difference;

This query uses variables to store the start date and end date, and then calculates the difference in months between them. The result will be:

date_difference
---------------
5

Full Example

Let's go through a complete example that includes creating a table, inserting data, and using the DATEDIFF() function.

Step 1: Creating a Table

This step involves creating a new table named subscriptions to store some sample data with subscription start and end dates.

CREATE TABLE subscriptions (
    id INT PRIMARY KEY,
    start_date DATETIME,
    end_date DATETIME
);

In this example, we create a table named subscriptions with columns for id, start_date, and end_date.

Step 2: Inserting Data into the Table

This step involves inserting some sample data into the subscriptions table.

INSERT INTO subscriptions (id, start_date, end_date) VALUES (1, '2024-01-01', '2024-06-01');
INSERT INTO subscriptions (id, start_date, end_date) VALUES (2, '2023-05-01', '2024-05-01');
INSERT INTO subscriptions (id, start_date, end_date) VALUES (3, '2022-08-01', '2024-08-01');

Here, we insert data into the subscriptions table.

Step 3: Using the DATEDIFF() Function

This step involves using the DATEDIFF() function to calculate the difference in days between the start_date and end_date columns.

SELECT id, start_date, end_date, DATEDIFF(day, start_date, end_date) AS duration_in_days
FROM subscriptions;

This query retrieves the id, start_date, end_date, and the difference in days between the start_date and end_date columns for each row in the subscriptions table. The result will be:

id  start_date          end_date            duration_in_days
--- ------------------- ------------------- -----------------
1   2024-01-01 00:00:00 2024-06-01 00:00:00 152
2   2023-05-01 00:00:00 2024-05-01 00:00:00 366
3   2022-08-01 00:00:00 2024-08-01 00:00:00 731

Conclusion

The SQL Server DATEDIFF() function is a powerful tool for calculating the difference between two dates based on a specified date part. Understanding how to use the DATEDIFF() function and its syntax is essential for effective date calculations and data processing in SQL Server.