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;
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.