Error message: “Adding a value to a ‘date’ column caused an overflow”

This is an error I ran across a little while ago, and thought it was worth a quick blog post (as a reminder to myself as much as anything).

The error came about because I was working with a date dimension, and I knew I would encounter cases where the date wasn’t exactly known but was known to be some point in the future. To try and handle these cases so that any WHERE SYSDATETIME() < DateColumn predicates would work correctly, I included a row for the 31st December 9999 in the date dimension. I ran some data into the table and tested a few simple queries and everything worked, but soon after a lot of other queries started to fail with the “Adding a value to a ‘date’ column caused an overflow” error.

This happened because I had some queries where I was joining to the date dimension and trying to return the next date with a DATEADD() function. Even though I was only running this on tables where the dates were in the past, SQL Server was starting the query execution by calculating the ‘tomorrow’ date against every row in the date table before joining the results to the main fact table. This resulted in the query trying to add a day to 9999-12-31, which would put the date out of range of the DATETYPE2 data type (and any data type).

Once I realised what was happening I removed the row from the date dimension and shifted to using NULL for the ‘sometime in the future’ DateID. This meant putting a bit of extra work into the query, but everything runs now.

As a bonus, it’s clearer what is happening. NULL is the correct marker to use in these scenarios, because it represents an unknown value. You may be able to get away with using a default value instead, but there’s always the danger that some future developer won’t realise that’s what’s happening and write code that will treat the default as if it’s a valid value.