Browse DevX
Sign up for e-mail newsletters from DevX


Best Practices for Date/Time Calculations in SQL Server

When can you use arithmetic operators in date/time calculations in SQL Server? When are the date/time functions provided by Microsoft the better option? Follow a few scenarios that demonstrate when arithmetic operators are safe and when they are risky.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

few months ago, I received a message from quite an experienced DBA and database programmer asking me whether I knew a nice trick for manipulating date/time data without using Microsoft SQL Server's date/time functions—something like GETDATE() + 10. It reminded me of a few internet forum discussions about the same subject in which participants were irked by the fact that Microsoft missed such a convenient feature. I decided that the topic deserved a deeper discussion. This article is the result.

How Is This Possible?
Try running the following example with the SQL Server DATEADD() function:

SELECT GETDATE(); SELECT DATEADD(dd, 1, GETDATE()); SELECT GETDATE() + 1; Results: 2007-03-15 16:21:41.630 2007-03-16 16:21:41.630 2007-03-16 16:21:41.630

As you can see, both the DATEADD() function and an addition operation that uses a plus sign (+) produce exactly the same result: they add one day to a current day and time. You'd expect that from DATEADD(), but why does an addition operation produce the same result? For the answer, you must explore the SQL Server date/time storage internals.

As you know, the datetime data type occupies eight bytes: four to store the number of days before or after January 01, 1900 and four more for the number of 3.33ms clock ticks since midnight. The smalldatetime data type needs only four bytes as it is less precise than datetime; it stores the number of days since January 01, 1900 and the number of minutes since midnight. All the numbers are stored as integers. So when you run SELECT GETDATE() + 1, you actually add datetime data to the integer number. Since the datetime data type has higher precedence, the integer number 1 (as an addend with lower precedence) has to be implicitly converted to a data type of the addend with higher precedence.

The following example converts "1" to datetime and then treats it as one day since Jan 01, 1900:

SELECT CAST(1 as datetime); Results: 1900-01-02 00:00:00.000

When you run SELECT GETDATE() + 1, you add two datetime values that internally are interpreted as integers. As a result an addition operation that uses a plus sign (+) becomes perfectly valid. For example, all the following statements will be correct:

SELECT GETDATE(); SELECT DATEADD(dd, 1, GETDATE()); SELECT GETDATE() + 1; SELECT GETDATE() + 'Jan 02, 1900'; Results: 2007-03-16 23:01:37.420 2007-03-17 23:01:37.420 2007-03-17 23:01:37.420 2007-03-17 23:01:37.420

Notice that usage of the addition operator (+) in date/time calculations requires at least one datetime addend, which also has to be an addend with the highest precedence.

Try the following example:

SELECT DATEADD(dd, 1, 'Mar 17, 2007'); SELECT 'Mar 17, 2007' + 1; SELECT 'Mar 17, 2007' + 'Jan 02, 1900'; SELECT GETDATE() + 1 + 'Jan 02, 1900';

As you can see, the first SELECT that uses SQL Server's DATEADD() function recognizes dates written as varchar and produces the correct result. The second SELECT fails with the error: "Conversion failed when converting the varchar value 'Mar 17, 2007' to data type int." This happens because varchar addend has lower precedence and must be implicitly converted to the integer data type. In this case such a conversion is impossible.

The third SELECT works, but doesn't make any sense. It just concatenates two varchar expressions. The forth SELECT is very interesting. Since second and third addends have lower precedence than the first one, they need to be implicitly converted to a datetime data type. Each of them will be interpreted as one day. Therefore two days will be added to the reult of the GETDATE() function.

Thanks for your registration, follow us on our social networks to keep up-to-date