On the Lambda

Programming, Technology, and Systems Administration

On the Lambda

SQL and Dates

July 13th, 2022 · No Comments · development, sql, Sql Server

I want to clear up a few common issues I often see related to date values in SQL.

Column Types

The first of these is storing dates in a string column type, like varchar or nvarchar. Schemas that do this need to be fixed, because they really are broken. When you do this, you don’t really have a date value at all anymore; you have a string value pretending to be a date. You lose the ability to directly call date functions like DATEPART(), DATEADD(), FORMAT(), etc, and you lose the ability to index the column properly, which can really cut to the core of your database performance. Even worse, you may find queries tending to need to convert these values for ALL of the rows in a table to find out to find out if they meet a WHERE condition or similar, even when you only need a few rows from a large table. Thanks to internationalization issues, these conversions are not the quick or simple operations you might think. The string value will also take up more storage and memory. Possibly worst of all, you open up your application to bugs where incorrect data makes its way into the column, because the database won’t be able to enforce the inputs.

Formats

Next up is formats for literals in the SQL code. I often see people use the preferred format for their own language or culture, whether it’s MM/dd/yyyy, dd/MM/yy, dd-MM-yyyy, or something else. It’s important to understand here that SQL is its own language, and just like any other language or culture it has its own expectations around date format. When writing SQL, it’s WRONG to put your own cultural expectations into this very different language. This applies to sample data and posts to public locations like Stack Overflow, too.

The correct format for SQL when you only have a date, with no time portion, is yyyyMMdd. Note the lack of any separators, which is slightly different than the common ISO-8601 yyyy-MM-dd format, but careful reading of ISO-8601 allows for an unseparated variant, so this is still compliant. When you have both date and time, the correct format is yyyy-MM-ddTHH:mm:ss[.fff] or yyyyMMdd HH:mm:ss[.fff]. (For clarity on what the capital letter portions of the format strings mean, I’m using the .Net DateTime format string reference). I’ll spare you repeating the details of why these are the only acceptible formats, but if you are interested you can find more info here.

Parameters

The last issue is using string concatenation to substitute a date value into an SQL query. This goes back to the whole SQL injection thing. I know some people believe you don’t need parameters for date and numeric values because the system will handle the formatting. This is wrong, and it’s still possible to manipulate some systems to format with arbitrary text for date and numeric data.

But I’m not even going to use that. I’ll assume you have some special magic escaper function that never makes a mistake. Even then, parameterized queries are still better and you still should not programmatically insert date or datetime strings into SQL code. I’m not even going to argue performance (though SQL Server is generally better at optimizing your query plans if you use a parameter instead of a varying string literal).

I am going to talk about correctness. Thanks to cultural/internationalization issues, converting back and forth between strings and dates (also numbers) is not only much slower than you’d expect, it’s also much more error-prone and much less of a solved problem then we’d like to believe. With a query parameter that is correctly typed as a DateTime column, and a DateTime object assigned to the parameter value, the ADO.Net library will correctly and efficiently handle these conversion issues for you. If you ever find yourself trying to format a DateTime variable for use in an SQL command string, you’re doing something very wrong. This is somewhat specific to the .Net ecosystem, but if you’re on a different platform you delude yourself if you believe the problem is handled much differently. Even with dynamic/loosely-typed platforms, which often work very hard to smooth over these issues, you will often find you can make significant gains by being more careful about handling dates.

Tags: ·

No Comments so far ↓

There are no comments yet...Kick things off by filling out the form below.

Leave a Comment