Unexpected Shorthand Date Implicit Conversions

Published on: 2018-10-23

I do my best work in the mornings.  Evenings are pretty good too once I get a second wind.

Late afternoon are my nemesis for getting any serious technical or creative work done.  Usually I reserve that time for responding to emails, writing documentation, and brewing coffee.

Some afternoons I can’t help myself though and end up getting myself into trouble.

What is THAT!?

It all started when I was troubleshooting an existing query that was having issues.  During the process of trying to understand what the query was doing, I happened to look at the execution plan:

Simplified, but you get the point.

When I hovered over the Table Scan, the Predicate section caught my eye.  Specifically, I wondered “Why is 1900-01-31 showing up? I don’t have that anywhere in my query!

(fun side story: the real query I was working on was dealing with user-defined datatypes, something I don’t have experience with, so I thought those had something to do with the problems I was experiencing.  I latched onto this 1900 date as the potential cause but it ended up being a red herring!)

Help!

Like I mentioned, late afternoons are not when I do my best work.  I couldn’t figure out why SQL Server was converting my -30 to January 31, 1900.

Intrigued and having no clue what was going on, I decided to post the question with the #sqlhelp hashtag on Twitter.  Fortunately for me, Aaron Bertrand, Jason Leiser, and Thomas LaRock all came to the rescue with ideas and answers – thanks guys!

Implicit Conversion

In hindsight, the answer is obvious: the -30 implicitly converts to a datetime (the return type of my @Today variable), in this case 30 days after the start of the minimum datetime value, 1900-01-01.

This makes perfect sense: SQL Server needs to do some math and in order to do so it first needs to make sure both datatypes in the equation match.  Since int readily converts to datetime but not the other way around, SQL Server was just doing its job.

Future Problems

As I mentioned earlier, this int to datetime conversion wasn’t the actual issue with my query – in my drowsy state I mistook it as being the source of my problem.

And while it wasn’t a problem this time, it can become a problem in the future.

Aaron has an excellent article on the problems with shorthand date math, but the most relevant future issue with my query is: what if someone in the future decides to update all datetimes to datetime2s (datetime2 being Microsoft’s recommended datatype for new work)?

If we update to a datetime2s and run the query again:

Everything is broken

AHHH!  While SQL Server had no problem converting our previous code between datetime and int, it’s not so happy about converting datetime2.

Morals

In the end, the above scenario had nothing to do with the actual problem I had on hand (which had to do with some operator precedence confusion).

To avoid future confusion and problems it’s still better to refactor the code to be explicit with what you want to do by using the DATEADD() function:

Thanks for reading. You might also enjoy following me on Twitter.

Want to learn even more SQL?

Sign up for my newsletter to receive weekly SQL tips!

4 thoughts on “Unexpected Shorthand Date Implicit Conversions”

  1. Hi Bert, very cool your blog and I always read.

    But, I stay a little confusion when you said that “, in this case 30 days after the start of the minimum datetime value, 1900-01-01.” about the Implicit Conversion.
    I say this, because I look at the BOL, and the date range of datetime is “January 1, 1753, through December 31, 9999” BUT, the value default is 1900-01-01. Because that default value is converted to 1900-01-31.
    Best regards,
    Vinicius Napoleão.

    1. Good point! I didn’t think about that at the time but I bet that’s why I was getting Thorne off – I thought the default would be the minimum value as well!

  2. Valuable nugget of education.
    Thanks —

    Also, I agree that it is good to recognize one’s energy flow throughout the day. I avoid scheduling mid-afternoon meetings where I need the participation from sharp minds.

    1. Thanks for reading! I agree – knowing one’s preferences (and also those of your coworkers) can help make everyone more productive.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.