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 😉)
I think that it’s wrong to use arithmetic operations with DATETIME. It’s risky and can lead to unexpected results, just like some of the examples you mentioned yourself.
SQL is a declarative language, which means you define what to do, and the database engine decides how to do it. DATETIME arithmetic operations break this rule, because they are based on the physical implementation of the DATETIME data type, and therefore they define not just what to do, but also how to perform the operation.
Theoretically, if Microsoft decides to change the physical implementation of the DATETIME data type, then these operations will not work anymore.
But I realize that this is just in theory. Let’s be practical. We have a well-defined interface, in the form of DATEADD, DATEDIFF, and other date/time functions, to perform date/time operations. The only drawback to these functions that you mentioned is that you need to write more code in some cases. But I think it also adds to the readability of your code.
So my honest opinion is that you should avoid DATETIME arithmetic operations altogether, and always use date/time functions instead. In fact, I think Microsft should have blocked this option, just like they did for DATETIME2.
Considering that countless business applications these days use syntax such as GETDATE() – 1, which is essentially the same thing as DATETIME arithmetic, I strongly believe that Microsoft will not be changing this functionality. Especially when they “moved on” with an entirely different data type – DATETIME2.
Secondly, it’s easy for you to say that the “only drawback” to the DATEADD and DATEDIFF functions is the need to “write more code in some cases”. But I think it’s much more substantial than that.
Take this scenario as an example: You want to display the difference between two datetime values (let’s say, something that should return the difference of “2d,10:23:45”). How would you recreate this functionality using DATEADD and DATEDIFF?
I spent about 20 minutes trying to recreate the same level of functionality of DATETIME arithmetics, and I gave up. Maybe it’s just me, but the level of complexity here is really not worth it in my opinion.
You wanna give it a try and let me know how it goes? 😄
You can achieve the same result with the following expression: DATEADD (SECOND , DATEDIFF (SECOND , @FirstDateTime , @SecondDateTime) , ‘1900-01-01’).
I know it’s an ugly code, and it’s less readable. But as I mentioned in my previous comment, it’s more “declarative”. It also gives you more flexibility. For example, you can easily decide whether you want to display the time difference in seconds or minutes or something else.
I see what you did there, although it’s not exactly what I was going for.
I was going for a way to show a *precise* difference between the two dates.
While your solution is limited to utilizing the difference between two dates based on a specific date-part (such as SECONDs), and theoretically you could utilize the smallest date-part to find the most accurate difference (nanoseconds, if I’m not mistaken)…
But I wouldn’t say that it “gives you more flexibility”. Because in order to actually implement such “flexibility” you would need to make the date-part dynamic somehow (seconds / milliseconds / minutes / etc.). But that could only be done using a bunch of CASE clauses, or even dynamic SQL.
based on my sad experience, DATETIME behavior changed between 2012, 2014 and 2016.
Also, STOP using DATETIME data type, it’s not aligned to the SQL standard. DATETIME2() is: https://docs.microsoft.com/en-us/sql/t-sql/data-types/datetime-transact-sql?view=sql-server-ver15. “Use the time, date, datetime2 and datetimeoffset data types for new work.”
Interesting. What kind of behavior changed between these versions?
Pingback: Even more fun with DATETIME arithmetics! – Eitan Blumin's Blog