Dates and Date Ranges in SQL Server
One of the most common tasks when working with data is to select data for a specific date range or a date. There are two issues that arise: calculating the date range and trimming the time portion in order to select the full days. Below are a few techniques to show how this can be done.
First, a look at the internals of the DATETIME data type in SQL Server. Its internal representation is as two 4-byte values. The first value represents the number of days since 01/01/1900. The second number is the number of milliseconds since midnight. Here is how those two values can be converted to VARBINARY and then to INT to extract the days and milliseconds portions.
-- The internal representation of a datetime is two 4-byte values
SELECT CURRENT_TIMESTAMP AS 'Today',
CAST(CURRENT_TIMESTAMP AS VARBINARY(8)) AS 'Two 4-byte Internal',
SUBSTRING(CAST(CURRENT_TIMESTAMP AS VARBINARY(8)), 1 , 4)
AS 'Days Since 1900-01-01',
SUBSTRING(CAST(CURRENT_TIMESTAMP AS VARBINARY(8)), 5 , 4)
AS 'Milliseconds Since Midnight',
CAST(SUBSTRING(CAST(CURRENT_TIMESTAMP AS VARBINARY(8)), 1 , 4) AS INT)
AS 'Days Represented as INT',
CAST(SUBSTRING(CAST(CURRENT_TIMESTAMP AS VARBINARY(8)), 5 , 4) AS INT)
AS 'Milliseconds Represented as INT';
Below are a few basic date calculations that show the power of the DATETIME functions and how to calculate key dates that can be used to extract data:
-- Basic dates and date calculations
SELECT CAST('19000101 00:00' AS SMALLDATETIME) AS 'Min SMALLDATETIME',
CAST('20790606 23:59' AS SMALLDATETIME) AS 'Max SMALLDATETIME',
CAST('1753-01-01T00:00:00.000' AS DATETIME) AS 'Min DATETIME',
CAST('9999-12-31T23:59:59.997' AS DATETIME) AS 'Max DATETIME',
CAST(0 AS DATETIME) AS 'Base SQL Server DATETIME',
CURRENT_TIMESTAMP AS 'Current Date/Time',
DATEDIFF(DAY, 0, CURRENT_TIMESTAMP)
AS 'Current Date/Time as Number (days since 1900-01-01)',
CAST(DATEDIFF(DAY, 0, CURRENT_TIMESTAMP) AS DATETIME)
AS 'Today as Date',
CAST(DATEDIFF(DAY, -1, CURRENT_TIMESTAMP) AS DATETIME)
AS 'Tomorrow as Date',
CAST(DATEDIFF(DAY, 1, CURRENT_TIMESTAMP) AS DATETIME)
AS 'Yesterday as Date',
DATEADD(YEAR, DATEDIFF(YEAR, 0, CURRENT_TIMESTAMP)-1, 0)
AS 'First Day of Last Year',
DATEADD(YEAR, DATEDIFF(YEAR, 0, CURRENT_TIMESTAMP), 0)
AS 'First Day of This Year',
DATEDIFF(DAY, 0, DATEADD(YEAR, -1, CURRENT_TIMESTAMP))
AS 'Today One Year Ago as Number',
CAST(DATEDIFF(DAY, 0, DATEADD(YEAR, -1, CURRENT_TIMESTAMP)) AS DATETIME)
AS 'Today One Year Ago as Date',
DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0)
AS 'First Day of Current Month',
DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP) + 1, 0)
AS 'First Day of Next Month',
DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP) - 1, 0)
AS 'First Day of Prior Month',
DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP) + 1, 0) - 1
AS 'Last Day of Current Month',
DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP), '01:30:00')
AS '1:30 am today',
CONVERT(VARCHAR(10), CURRENT_TIMESTAMP, 101) + ' 11:30:00.000'
AS '11:00 am today',
CONVERT(VARCHAR(10), CURRENT_TIMESTAMP, 101) + ' 17:00:00.000'
AS '5:00 pm today',
DATEADD(WEEK, DATEDIFF(WEEK, 0, CURRENT_TIMESTAMP), 0)
AS 'First Day of This Week',
DATEADD(WEEK, DATEDIFF(WEEK, 0, CURRENT_TIMESTAMP) - 1, 0)
AS 'First Day of Last Week',
DATEADD(WEEK, DATEDIFF(WEEK, 0, CURRENT_TIMESTAMP) + 1, 0)
AS 'First Day of Next Week';
Let’s implement some date range searches to see this in action. Given table Orders with DATETIME data type column order_date, here are a few range selections:
-- Get Orders for Last Month
SELECT order_date
FROM Orders
WHERE order_date >= DATEADD(MONTH,
DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP) - 1, 0)
AND order_date < DATEADD(MONTH,
DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0);
-- Get Orders for Current Month
SELECT order_date
FROM Orders
WHERE order_date >= DATEADD(MONTH,
DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0)
AND order_date < DATEADD(MONTH,
DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP) + 1, 0);
-- Get Year to Date Orders
SELECT order_date
FROM Orders
WHERE order_date >= DATEADD(YEAR,
DATEDIFF(YEAR, 0, CURRENT_TIMESTAMP), 0)
AND order_date < DATEDIFF(DAY, -1, CURRENT_TIMESTAMP);
-- Get Month to Date Orders
SELECT order_date
FROM Orders
WHERE order_date >= DATEADD(MONTH,
DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0)
AND order_date < DATEDIFF(DAY, -1, CURRENT_TIMESTAMP);
-- Get Last Year's Orders
SELECT order_date
FROM Orders
WHERE order_date >= DATEADD(YEAR,
DATEDIFF(YEAR, 0, CURRENT_TIMESTAMP)-1, 0)
AND order_date < DATEADD(YEAR,
DATEDIFF(YEAR, 0, CURRENT_TIMESTAMP), 0);
-- Get Today's Orders
SELECT order_date
FROM Orders
WHERE order_date >= DATEDIFF(DAY, 0, CURRENT_TIMESTAMP)
AND order_date < DATEDIFF(DAY, -1, CURRENT_TIMESTAMP);
-- Get Yesterday's Orders
SELECT order_date
FROM Orders
WHERE order_date >= DATEDIFF(DAY, 1, CURRENT_TIMESTAMP)
AND order_date < DATEDIFF(DAY, 0, CURRENT_TIMESTAMP);
-- Get Today's Orders Between 9:00 am And 11:00 am
SELECT order_date
FROM Orders
WHERE order_date BETWEEN
CONVERT(VARCHAR(10), CURRENT_TIMESTAMP, 101) + ' 09:00:00.000'
AND CONVERT(VARCHAR(10), CURRENT_TIMESTAMP, 101) + ' 11:00:00.000';
-- Or
SELECT order_date
FROM Orders
WHERE order_date BETWEEN
DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP), '09:00:00')
AND DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP), '11:00:00');
-- Get Orders for Last Week
SELECT order_date
FROM Orders
WHERE order_date >= DATEADD(WEEK,
DATEDIFF(WEEK, 0, CURRENT_TIMESTAMP) - 1, 0)
AND order_date < DATEADD(WEEK,
DATEDIFF(WEEK, 0, CURRENT_TIMESTAMP), 0);
-- Get Orders for Current Week
SELECT order_date
FROM Orders
WHERE order_date >= DATEADD(WEEK,
DATEDIFF(WEEK, 0, CURRENT_TIMESTAMP), 0)
AND order_date < DATEADD(WEEK,
DATEDIFF(WEEK, 0, CURRENT_TIMESTAMP) + 1, 0);
-- Get Orders for One Year Back From Current Date
SELECT order_date
FROM Orders
WHERE order_date >= DATEDIFF(DAY, 0,
DATEADD(YEAR, -1, CURRENT_TIMESTAMP))
AND order_date < DATEDIFF(DAY, -1, CURRENT_TIMESTAMP);
There could be many more variations based on the same techniques to calculate dates. All those techniques use the datetime functions DATEADD and DATEDIFF to trim the time portion of the date in order to get accurate results. The key to this is the behavior of DATEDIFF to calculate difference between boundaries (that is when crossing between years, months, days, minutes, etc.). For example, difference in days between May 3, 2006 at 11:59 PM and May 4, 2006 at 00:01 AM returns one day, even it is only a couple minutes. Similar, calculating difference in years between Dec 31, 2006 and Jan 1, 2007 returns one year, even it is only a day or less. With this in mind it is very easy to accomplish the timing effect. Calculating difference one level higher trims the lower level. For example, to trim second calculate difference in minutes, to trim hours (or time in general) calculate difference in days, difference between months will trim days, and so on.
The same trimming can be done by converting to a string and then back to a date. But that is a slower and lengthy way of doing it. Here is a brief example of both methods:
-- Trim time portion
DECLARE @date DATETIME;
SET @date = CURRENT_TIMESTAMP;
-- Convertmg to string and back to date
SELECT CONVERT(DATETIME, CONVERT(CHAR(10), @date, 101)) ;
-- Using datetime functions
SELECT DATEADD(DAY, DATEDIFF(DAY, 0, @date),0);
As a bonus, here is how to convert time stored as a decimal number (like 5.25, 5.50, or 5.75) to a time format (like 5:15, 5:30, or 5:45):
-- Format Time From 5.25 to 5:15
-- Decimal Format to Time Format
DECLARE @time DECIMAL(5, 2);
SET @time = 5.25;
SELECT CONVERT(CHAR(5), DATEADD(ss, @time * 3600, 0), 108);
Just for clarification: the CURRENT_TIMESTAMP function returns the current date and time. This function is the ANSI SQL equivalent to the GETDATE function.