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
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:
Into something like this:
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?
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 😉)