About my database date datatype crusade: Valid? Worthwhile? Does anyone else feel it?

Question

I spend a lot of time answering SQL questions over on SO. I frequently come across queries of this ilk:

SELECT * FROM person WHERE birthdate BETWEEN '01/01/2017' AND '01/03/2017'

SELECT * FROM person WHERE birthdate BETWEEN '2017-01-01' AND '2017-03-01'

SELECT * FROM person WHERE birthdate BETWEEN 'some string' AND 'other string'

i.e. either relying on an implicit conversion from string to date (bad), of the given parameters or relying on the database converting x million database row values to string and doing a string compare (worse)

I occasionally make a comment, particularly if it's a high rep user who writes a smart answer, but whom I feel really should be being less sloppy/stringly typed with their data types

The comment usually takes the form that it would probably be better if they explicitly converted their strings to dates, using to_date (Oracle), str_to_date (MySQL), convert (SQLSERVER) or some similar mechanism:

    --oracle
    SELECT * FROM person WHERE birthdate BETWEEN TO_DATE('20170101', 'YYYYMMDD') AND TO_DATE('20170301', 'YYYYMMDD')

    --mysql
    SELECT * FROM person WHERE birthdate BETWEEN STR_TO_DATE('20170101', '%Y%m%d') AND STR_TO_DATE('20170301', '%Y%m%d')

    --SQLS, ugh; magic numbers
    SELECT * FROM person WHERE birthdate BETWEEN CONVERT(datetime, '20170101', 112) AND CONVERT(datetime, '20170301', 112)

My technical justifications for doing so is that it's explicit as to the format of the date, and ensures that the few source parameters definitely become the datatype of the target column. This prevents any possibility that the database will get an implicit conversion wrong (the 3rd Jan/1st Mar argument of the very first example) and it prevents the db deciding to convert a million date values in the table to strings (using some server specific date formatting that might not even match the format of the date in the string parameters within the sql) in order to do the compare - horrors abound

My social/academic justification for doing so is that SO is a learning site; people on it acquire knowledge either implicitly or explicitly. To hit a newbie with this query as an answer:

SELECT * FROM person WHERE birthdate BETWEEN '2017-01-01' AND '2017-03-01'

Might lead them to think this is sensible, adjusting the date for some format they prefer:

SELECT * FROM person WHERE birthdate BETWEEN '01/01/2017' AND '01/03/2017'

If they at least saw some explicit attempt to convert the date, they might start doing it for their weird date format, and kill some forever-bugs before they arise. After all, we (I) try and dissuade people from getting into the SQL injection habit (and would anyone advocate parameterising a query and then declaring to the driver that @pBirthdate is a string, when the frontend has a datetime type?)

Back to what happens after I make my recommendation: I usually get some pushback to the "be explicit, use x" recommendation, like "everyone else does it", "it always works for me", "show me some manual or reference doc that says I should be explicit" or even "what??"

I've asked, in response to some of these, whether they'd search an int column by doing WHERE age = '99' passing the age as a string. "Don't be silly, we don't need to put ' when searching int" comes the response, so there is some appreciation for different data types in their mind somewhere, but perhaps just no connection to the logical leap that searching an int column by passing a string (apparently silly) and searching a date column by passing a string (apparently sensible) is hypocrisy

So in our SQLs we have a way to write things as numbers (use numerics, without delimiters), things as string strings (use anything between apostrophe delimiters).. Why no delimiters for dates? It's such a fundamental data type in most DB? Could this whole thing could maybe be solved just by having a way of writing a date in the same way javascript lets us specify a regex by putting / either side of some characters. /Hello\s+world/. Why not have something for dates?

Actually, to my knowledge, (only) Microsoft Access actually has symbols that indicate "a date has been written between these delimiters" so we can get a good shortcut like WHERE datecolumn = #somedate# but the date presentation is still liable to give problems e.g mm/di vs dd/mm, because MS have always played fast and loose with the stuff the VB crowd thought was a good idea

---

Back to the main point: I'm arguing that it's wise to be explicit with this medium that forces us to pass a multitude of different datatypes as strings..

Is it a valid assertion?

Should I continue this crusade? Is it a valid point that stringly typing is a modern no-no? Or will every RDBMSs (including ancient versions) out there, when thrust a query WHERE datecolumn = 'string value' absolutely certainly correctly convert the string to a date and do the search without converting table data/losing use of indexes? I suspect no, at least from personal experience of Oracle 9. I suspect also that there may be some get-away-with-it scenarios if strings are always written in some ISO standard format, and the column is some date flavour, then the string parameter will always be correctly implicitly converted. Does this make it right?

Is it a worthwhile task?

Many people don't seem to get it, or don't care, or exhibit some hypocrisy in that their ints are ints but their dates are strings.. Common to most though is that few people have ever turned round and said "you know what, I agree with your point. I'll be explicit about my dates from now on".

Answer 1

You wrote:

are those parameters 1st Jan to 3rd Jan, or 1st Mar..

That is indeed a potential source of errors. Pointing this out to an asker may helpful to other readers, so yes, this is a valid concern. However, to be constructive, I would

• refer to ANSI SQL and use the DATE or DATETIME literals from that standard

• use the usual, unambiguous datetime format of a specific DBMS (and mention which SQL dialect is used)

Unfortunately, not every DBMS supports ANSI SQL date literals in exactly the similar manner (if they support it at all), so this will typically lead to a variant of the second approach. The fact "the standard" is not rigidly implemented by different DB vendors is probably part of the problem here.

Note further, for many real world systems, people can actually rely on a specific, fixed locale on the database server, even if the client applications are localized, because there is just one kind of server, always configured in the same way. So '01/03/2017' can be often assumed to have the fixed format 'dd/mm/yyyy', or 'mm/dd/yyyy' for any SQL used on the specific system they are working with. So if someone telling you "it always works for me", this maybe indeed a sensible answer for his environment. If this is the case, it makes it less worthwile to discuss this topic.

Talking about "performance reasons": as long as there are no measurable performance problems, this is quite superstitious to argue with "potential performance issues". If a database is doing a million string-to-date conversions or not probably does not matter when the time difference is just 1/1000 second, and the real bottleneck is the network which causes the query to last 10 seconds. So better put these concerns aside as long as someone asks explicitly for performance considerations.

Should I continue this crusade?

I tell you a secret: I hate religious wars. They don't lead to anything useful. So, if ambigious date/time specs in SQL might lead to problems, mention them, but don't try to force people to be more rigid if it does not really bring them any benefits in their current context.

Answer 2

Your crusade does not solve the problem.

There are two separate issues:

• implicit type conversion in SQL

• ambiguous date formats like 05/06/07

I see where you are coming from with your crusade, but I don't think the explicit conversion actually solves the problem at hand:

• Implicit conversion still happen in case of a mismatch between the types in a comparison. If a string is compared to a date, SQL will attempt to convert the string to a date first. So comparing a date-type column to an explicitly converted date value is exactly the same as comparing to a date in string-format. Only difference I see is if you compare a date value to a column which does not actually contain dates but strings - but this would be an error in any case.

• Using explicit conversion does not solve the ambiguity in non-ISO date formats.

The only solution i see:

• don't compare string-type columns to non-string values.
• only ever use ISO type date formats.

And of course, don't ever store dates in a string-type column. But again, explicit conversion of date literals will not prevent this.

Arguably, implicit conversions was a mistake in SQL, but given how the language is designed, I do not see the benefit of the explicit conversion. It will not avoid implicit conversion anyway, and it only makes the code more difficult to read and write.

Answer 3

First and foremost, you do have a point. Dates should not be put into strings. Database engines are complex beasts where you are never 100% certain what exactly will happen under the hood given an arbitrary query. Converting to dates makes things unambiguous and can increase performance.

BUT

It isn't a problem worth the extra thought-effort to solve for most people. If it were easy to use date literals in a query, it would be easy to defend your position. But it isn't. I mostly use SQL Server, so trying to remember that mess to convert a date just isn't happening.

For most people, the performance gain is negligible. "Why yes Mr. Boss-man, I did spend an extra 10 minutes fixing this simple bug (I had to google how to convert dates because that syntax is ... special ...). But I saved an extra 0.00001 seconds on a rarely executed query." That's not going to fly most places I've worked at.

But it removes ambiguity in date formats you say. Again, for a lot of applications (company internal applications, local government stuff, etc. etc.) it isn't really a concern. And for those applications where it is a concern (large, international or enterprise applications), that either becomes a UI / business layer concern or those companies already have a team of well versed DBAs who already know this. TL/DR: if internationalization is a concern, someone is already thinking about it and has already done as you suggest (or has otherwise mitigated the issue).

So What Now?

If you feel so inclined, keep fighting the good fight. But don't be surprised if most people don't feel this is important enough to worry about. Just because there are situations where it matters, doesn't mean that that is everyone's situation (and it likely isn't). So don't be surprised when you get some push back for something that is technically-correct-and-better-but-not-really-relevant.

Answer 4

I'm arguing that it's wise to be explicit with this medium that forces us to pass a multitude of different datatypes as strings.

Assuming that "dates" are being passed around "in" Strings then yes; I absolutely agree that you're right to do this.

When is "01/04/07"?
* January 4th?
* April 1st?
* April 7th [2001]?

Any or all of these might be correct, depending on how "the computer" chooses to interpret them.

If you have to build dynamic SQL with literals in them, then your date formatting has to be well-defined and, preferably, machine-independent (I had a weird one on a Windows Server where date-based processing within a Windows Service went awry because an operator logged onto the console with different date format preferences!). Personally, I exclusively use[d] the format "yyyy-mm-dd".

However ...

The best solution is to use Parameterised Queries which force the data type to be converted before SQL gets involved - getting a "date" value into a Date Parameter forces the type conversion early on (making it purely a coding problem, not a SQL one).