Fun with DATETIME Arithmetics

The Zeroth Value

The datetime data type has a “zero value”, which is represented as 1900-01-01 00:00:00.

It can be represented by the literal value of 0. Check it out:

SELECT CONVERT(datetime, 0)

This outputs the value 1900-01-01 00:00:00.000

You could think of the datetime data type as the number of days since 1900-01-01.
It can also be decimal as well as negative:

SELECT
  CONVERT(datetime, 1.5)
, CONVERT(datetime, -3.5)

This would output 1900-01-02 12:00:00.000 and 1899-12-28 12:00:00.000 respectively.

What is in a DATEADD?

Check out the snippet below:

DECLARE @d1 DATETIME, @d2 DATETIME
SET @d1 = 0
SELECT @d1 -- result: 1900-01-01 00:00:00.000
SET @d1 = DATEADD(day, 1, @d1)
SELECT @d1 -- result: 1900-01-02 00:00:00.000
SET @d1 = DATEADD(hour, 3, @d1)
SELECT @d1 -- result: 1900-01-02 03:00:00.000
SET @d1 = DATEADD(minute, 35, DATEADD(second, 15, @d1))
SELECT @d1 -- result: 1900-01-02 03:35:15.000

Starting with the “zeroth” datetime value, we were able to gradually “add” dateparts to it until we received some kind of complex value.

We can also add time and datetime literals like this:

SET @d1 += '10:30.5'
SELECT @d1 -- result: 1900-01-02 14:05:15.500
SET @d1 += '1900-01-01 2:10.4'
SELECT @d1 -- result: 1900-01-02 16:15:15.900

Mathematical addition and subtraction can be performed between two datetime data types:

SET @d2 = '1900-03-30 18:00'
SELECT
   @d1 + @d2 -- result: 1900-04-01 10:15:15.900
 , @d1 - @d2 -- result: 1899-10-05 22:15:15.900
 , @d2 - @d1 -- result: 1900-03-29 01:44:44.100

This means that we can have basic datetime arithmetics in SQL server. We can use subtraction to find an accurate difference between two dates, and use addition to add an accurate interval to a datetime column or variable.

What about datetime2?

It’s important to note that the datetime2 data type does not support the same functionality.

When trying to perform arithmetics with it, you’d get something like the following errors:

Msg 8117, Level 16, State 1, Line 12
Operand data type datetime2 is invalid for add operator.

Msg 402, Level 16, State 1, Line 12
The data types datetime2 and datetime are incompatible in the add operator.

So, if you have some datetime2 data types, you’d have to convert them to datetime before doing any of what you see here.

Any plans for DATEMULTIPLY and DATEDIVIDE?

Multiplication (*) and division (/) won’t work:

SET @d2 = @d1 * 2.0

Msg 257, Level 16, State 3, Line 21
Implicit conversion from data type datetime to numeric is not allowed. Use the CONVERT function to run this query.

But it’s possible if you convert the datetime values to float first (although I’m not entirely sure why you would ever need to do something like this):

SET @d2 = CONVERT(float, @d1) * 2.0

SELECT @d2 -- result: 1900-01-04 08:30:31.800

SELECT CONVERT(datetime, CONVERT(float, @d1) * CONVERT(float, @d2))
-- result: 1900-01-06 15:02:05.420

EDIT: Check out part 2 for a use case for using multiplication and division on datetime.

When does it go wrong?

Let’s try playing around with quarters a bit:

SELECT
 DATEADD(quarter, 1, 0), -- result: 1900-04-01 00:00:00.000
 DATEADD(quarter, 2, 0), -- result: 1900-07-01 00:00:00.000
 DATEADD(quarter, 3, 0)  -- result: 1900-10-01 00:00:00.000

Looks reasonable so far. Each quarter appears to be the equivalent of 3 months, as expected.

But what if we complicate things a bit, like adding a quarter to an existing datetime and then use subtraction to see what the datetime difference looks like?

DECLARE @dt datetime = '2021-03-25'

SELECT DATEADD(quarter, 1, @dt) - @dt
-- result: 1900-04-03 00:00:00.000

Wait, this looks wrong…. Why is the day part equal to “3” all of a sudden? Isn’t it supposed to remain “1”? I only wanted to add a few months… Why was the day affected?

Let’s try and break it down:

SELECT DATEADD(quarter, 1, @dt), @dt

This outputs 2021-06-25 and 2021-03-25.
That’s weird… Looks like a simple difference of 3 months, as expected… So where did those 2 extra days come from?

Let’s try and break it down even further:

SELECT

 DATEADD(month, 3, 0), -- result: 1900-04-01 00:00:00.000

 @dt + '1900-04-01', -- lets try adding 3 months. result: 2021-06-23

 DATEADD(month, 3, @dt) -- is it really the same as DATEADD? result: 2021-06-25

Interesting. When trying to use the arithmetic method to add 3 months instead of DATEADD, we got the datetime value 2021-06-23 which is indeed missing a couple of days compared to the expected 2021-06-25.

This is happening because months can consist of 28, 29, 30, or 31 days, and therefore have “inconsistent” number of days within them. One month is not always the equivalent of another month.

Conclusion: datetime arithmetics could be comparable to using DATEDIFF/DATEADD but only as long as you’re utilizing date parts that have a “consistent size“. Months and quarters, therefore, wouldn’t work as expected. Years would be problematic as well due to leap years with 366 days instead of 365.

Can you make it look nicer?

As you probably noticed, that datetime interval is not very good-looking.

That 1900-whatever thing can be difficult to read, especially when the interval is more than a few days.

However, using some trickery with CONVERT and DATEDIFF functions, we can prepare for ourselves a handy scalar function that turns anything like this:

1900-03-05 11:22:33

Into something like this:

63d,11:22:33:000

Something like the function below should do the trick nicely:

CREATE OR ALTER FUNCTION dbo.FormatInterval (@dt DATETIME)
RETURNS VARCHAR(100)
WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT
AS
BEGIN
RETURN
 ISNULL(NULLIF(CONVERT(varchar(100), DATEDIFF(dd,0, @dt)), 0) + 'd,', '')
 + CONVERT(varchar(100), @dt, 114)
END

So what is this even good for?

We can use datetime arithmetics as a versatile and robust alternative to DATEADD.

For example, let’s say we wanted to create a function that generates periods for a time series using a parameter that determines the interval between each period.
When using DATEADD, we would have to explicitly use specific date parts, and that would limit us to a pre-defined list of “period types”. For example:

DECLARE
	@FromDate DATETIME = CONVERT(DATE, GETDATE()-1),
	@EndDate DATETIME = GETDATE(),
	@PeriodType CHAR(2) = 'H'
/*
Supported period types:
	MI - Minute
	H - Hour
	D - Day
	W - Week
	M - Month
	Q - Quarter
	T - Trimester
	HY - Half-Year
	Y - Year
*/
;
WITH Periods
AS
(
	SELECT 
		PeriodNum = 1,
		StartDate = @FromDate,
		EndDate = 
			CASE @PeriodType
				WHEN 'MI' THEN
					DATEADD(minute,1,@FromDate)
				WHEN 'H' THEN
					DATEADD(hh,1,@FromDate)
				WHEN 'D' THEN
					DATEADD(dd,1,@FromDate)
				WHEN 'W' THEN
					DATEADD(ww,1,@FromDate)
				WHEN 'M' THEN
					DATEADD(mm,1,@FromDate)
				WHEN 'Q' THEN
					DATEADD(Q,1,@FromDate)
				WHEN 'T' THEN
					DATEADD(mm,4,@FromDate)
				WHEN 'HY' THEN
					DATEADD(mm,6,@FromDate)
				WHEN 'Y' THEN
					DATEADD(yyyy,1,@FromDate)
			END
		
	UNION ALL
		
	SELECT
		PeriodNum = PeriodNum + 1,
		StartDate = EndDate,
		EndDate = 
			CASE @PeriodType
				WHEN 'MI' THEN
					DATEADD(minute,1,EndDate)
				WHEN 'H' THEN
					DATEADD(hh,1,EndDate)
				WHEN 'D' THEN
					DATEADD(dd,1,EndDate)
				WHEN 'W' THEN
					DATEADD(ww,1,EndDate)
				WHEN 'M' THEN
					DATEADD(mm,1,EndDate)
				WHEN 'Q' THEN
					DATEADD(Q,1,EndDate)
				WHEN 'T' THEN
					DATEADD(mm,4,EndDate)
				WHEN 'HY' THEN
					DATEADD(mm,6,EndDate)
				WHEN 'Y' THEN
					DATEADD(yyyy,1,EndDate)
			END
	FROM
		Periods
	WHERE
		EndDate < @EndDate
)
SELECT PeriodNum, StartDate, EndDate
FROM Periods
OPTION (MAXRECURSION 0);

But when using DATETIME arithmetics, we can be more flexible with our intervals, and even simplify our code. For example, we can generate 10-minute intervals like so:

DECLARE
	@FromDate DATETIME = CONVERT(DATE, GETDATE()-1),
	@EndDate DATETIME = GETDATE(),
	@Interval DATETIME = '00:10:00'
;
WITH Periods
AS
(
	SELECT 
		PeriodNum = 1,
		StartDate = @FromDate,
		EndDate = @FromDate + @Interval
		
	UNION ALL
		
	SELECT
		PeriodNum = PeriodNum + 1,
		StartDate = EndDate,
		EndDate = EndDate + @Interval
	FROM
		Periods
	WHERE
		EndDate < @EndDate - @Interval
)
SELECT PeriodNum, StartDate, EndDate
FROM Periods
OPTION (MAXRECURSION 0);

This is much easier on the eyes, right?

However, as mentioned previously, we wouldn’t be able to reliably use month-dependent intervals such as months, quarters, trimesters, or years. So, there is indeed a sort of trade-off here. Although… One could think of a way to write a super-robust code here that could enjoy both worlds, using some CASE WHEN magic (even though it won’t save us code lines).

We can also use datetime arithmetics as an alternative to DATEDIFF as well.

For example, we can use it to display the duration of a command in an easily understood representation:

SELECT
  session_id, start_time, command
, duration =
	ISNULL(NULLIF(CONVERT(varchar(100), DATEDIFF(dd,0, GETDATE() - start_time)), 0) + 'd,', '')
	+ CONVERT(varchar(100), GETDATE() - start_time, 114)
FROM sys.dm_exec_requests

Why settle for just seconds or milliseconds, am I right?

Conclusion

Being familiar with the strengths of datetime arithmetics can potentially save us a lot of headaches related to the limitations of DATEADD and DATEDIFF.

It can help us with writing cleaner and leaner code, and by doing so help us become even more productive.

I personally started using these tricks in all kinds of use cases, and I’m really loving this new habit.

(almost as much as I love the WHILE (true) thing 😉)

EDIT: But wait, there’s more! Check out part 2!