How to manage dates in T-SQL ? SQL Server

T-SQL scripts to manage dates using the filter, format and calculate SQL Server functions.

Working with dates is an essential part of any SQL developer’s skillset, as dates and times are common data types used in a variety of applications. This tutorial aims to provide a comprehensive guide on managing dates in T-SQL using a sample sales table with date columns. Various techniques for filtering, formatting, and calculating dates will be explored, while keeping the content both technical and easy to understand. Let’s dive in!

1. Set up the SQL Server sample sales table

Before beginning, it is necessary to create a sample sales table with some date columns to work with. Here’s the script to create the table and insert some sample data:

CREATE TABLE Sales (
    OrderID INT PRIMARY KEY,
    ProductID INT,
    OrderDate DATE,
    ShipDate DATE
);

INSERT INTO Sales (OrderID, ProductID, OrderDate, ShipDate)
VALUES
    (1, 101, '2022-01-03', '2022-01-08'),
    (2, 102, '2022-01-04', '2022-01-09'),
    (3, 103, '2022-01-05', '2022-01-10'),
    (4, 104, '2022-01-06', '2022-01-11'),
    (5, 105, '2022-01-07', '2022-01-12');

2. Manage data based on dates

It is often necessary to filter data based on date conditions. The following are 2 examples:

One using a date range
And one using a single date

2.1 Find sales within a specific date range

To find sales within a specific date range, use a WHERE clause along with the BETWEEN operator to filter records based on the desired date range. This allows retrieval of sales data that falls within the specified start and end dates.

SELECT *
FROM Sales
WHERE SaleDate BETWEEN '2022-01-03' AND '2022-01-05';

2.2 Find sales on a specific day

To find sales on a specific day, use a WHERE clause with the equality operator to filter records based on the specific date.

SELECT *
FROM Sales
WHERE SaleDate = '2022-01-04';

3. Format dates in T-SQL

Formatting dates is useful when displaying them in a more human-readable form. The T-SQL built-in CONVERT function can be used for this purpose. The official standard for dates is the ISO 8601 standard.

SELECT
    SaleID,
    ProductID,
    CONVERT(VARCHAR, SaleDate, 103) AS FormattedSaleDate, -- dd/mm/yyyy format
    SalePrice
FROM Sales;

4. Date calculations with SQL Server

Date calculations are essential for analyzing data over time. Some examples include:

4.1 Calculate the difference between two dates

To calculate the difference between two dates, use the DATEDIFF function, which takes the date part, start date, and end date as arguments. This returns the difference between the specified dates in the desired units (e.g., days, months, years).

DECLARE @date1 DATE = '2022-01-01';
DECLARE @date2 DATE = '2022-01-31';

SELECT DATEDIFF(DAY, @date1, @date2) AS DaysDifference;

How to calculate the difference between two dates with T-SQL?

4.2 Add or subtract days from a date

To add or subtract days from a date, use the DATEADD function. This function takes three arguments: the date part (e.g., day, month, year), the number to add or subtract, and the date from which to perform the operation.

SELECT
    OrderID,
    OrderDate,
    DATEADD(day, 7, OrderDate) AS MaxShippingDate
FROM
    Sales;

In the example above, seven days are added to the original OrderDate, resulting in a new column called MaxShippingDate. In real life this date could be the last date the company can ship the product to respect the delivery deadlines.

5. Calculate the difference between two dates

To calculate the difference between two dates, use the DATEDIFF function. This function takes three arguments: the date part, the start date, and the end date.

SELECT
    OrderID,
    OrderDate,
    ShipDate,
    DATEDIFF(day, OrderDate, ShipDate) AS DaysBetween
FROM
    Sales;

In this example, the number of days between the OrderDate and ShipDate is calculated and displayed in a new column called DaysBetween. In a practical case it can the actual difference between the order date and the shipping date. It can be used with the maximum shipping date, for example, to calculate key performance indicators on compliance with delivery schedules.

6. Format dates with a specific style numer

To format dates, use the CONVERT function, which allows specifying a style for the date format. The function takes three arguments: the data type to convert to (e.g., VARCHAR), the date to convert, and the style number. In the following example, the OrderDate is converted to a formatted string using the SQL Server date style number 103 : i.e. with the dd/mm/yyyy format.

SELECT
    OrderID,
    OrderDate,
    CONVERT(VARCHAR, OrderDate, 103) AS FormattedOrderDate
FROM
    Sales;

Conclusion on managing dates with T-SQL scripts

This tutorial has provided a comprehensive guide on managing dates in T-SQL using a sample sales table. The techniques demonstrated here, such as filtering, formatting, and calculating dates, are crucial for any SQL developer working with date data. By mastering these skills, one can effectively analyze and manipulate date information to meet various business requirements.