13

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".

  • I have even seen someone getting problems with WHERE datecolumn = 01/02/12'` where it is possible they are asking for the year 1912, 2012, 2001, 1901, 12 or 1. It is also a problem outside of the database world, the number of programmers who can't understand why converting "09" to an int is causing a crash are legion, 9 is not a valid octal digit and a leading 0 makes the string octal in a lot of systems – Steve Barnes Sep 6 '17 at 6:16
  • 2
    I did think about extending my example to ask whether WHERE age = '0x0F' is a valid way to hope a database will search for fifteen year olds.. – Caius Jard Sep 6 '17 at 8:52
  • 1
    I removed a question that is off-topic here - we don't do resource requests. One of the 2 close votes was given for this reason. Otherwise, I think this is a valid question, although it might border on being too broad. I hope that the removal of the off-topic question helps to narrow things down a little. – Thomas Owens Sep 6 '17 at 10:42
  • TL;DR but in production systems, I would expect dates like this to almost always be in parameters. Hardcoding dates into queries is a bigger problem than whether you use implicit conversions. If I'm writing some throw away query, it either works or it doesn't. I never do this anyway (because I can never remember the default date format) but I'm not sure it matters much. – JimmyJames Sep 6 '17 at 13:47
  • 1
    Life is about picking your battles. In my view, this one just isn't worth fighting... – Robbie Dee Sep 6 '17 at 14:31
7

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.

  • This is not so much a question about the ambiguity of American vs Sensible date formats though. It's about whether it's sensible to pass dates in a SQL statement as a string, and rely on implicit conversion to date. The question of the database having to do a million date->str conversions for all million rows is one performance aspect, and it might only take 1/1000th of a second for one query, but now imagine it in the context of thusands of concurrent users. The bigger performance issue is that converting data means indexes can no longer be used and that can be really serious – Caius Jard Sep 6 '17 at 8:40
  • @CaiusJard: my answer stands: it is sometimes sensible, and sometimes not, it depends on the context. And honestly, I refuse to "... imagine ..." anything here. When it comes to performance, discussing any hypothetical case is not useful. When there are measurable performance issues, then it is time to optimize, and sometimes to micro-optimize, not beforehand. – Doc Brown Sep 6 '17 at 9:22
  • It's interesting that you see it as hypothetical; I see relying on implicit behaviour as a clear opportunity for bugs and performance complications to arise (for well documented reasons: indexes don't work if the entire column data is transformed before it is searched), and with explicit instructions these cannot happen – Caius Jard Sep 6 '17 at 9:31
  • @CaiusJard: don't play with words - with "hypothetical" I don't mean "unlikely", I used the term for any kind of imagined scenario, opposed to "real existing situation" where one can measure what happens. – Doc Brown Sep 6 '17 at 10:48
  • 1
    @CaiusJard: if you want to impress other industry professionals, you should know exactly why "performance optimization" is very different from "security optimization", and that is exactly my point here - performance issues can be handled after they occur, that is seldom too late. Security issues not, they should be thoroughly avoided before they occur. So please don't compare apples with oranges. If you like crusades, security arguments are much better suited for this ;-) – Doc Brown Sep 7 '17 at 19:08
5

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.

  • True. Perhaps I should point it out from this perspective, that the most sensible thing to do is ensure that the datecolumn operand and the value operand have the same datatype (be it string, date, whatever). I do specifically make this recommendation only in questions where I know the table column is DATETIME and their example answer is using a string operand with implicit conversion .. – Caius Jard Sep 6 '17 at 11:08
  • Something doesn't sit right with me on this answer. You make some interesting points but I feel like the conclusion is idealistic. From a design perspective, yes, non-ISO date formats are ambiguous to the human eye but if using explicit conversion, syntactically it is not ambiguous to the parser. Likewise, many ETL processes involving dates are going to require some comparison (in the form of a file import) of a string to the date format of the database. Trying to eliminate string to date comparisons seems unrealistic to me. – DanK Sep 7 '17 at 13:10
  • @DanK: ETL is a different issue - if you are reading data from a CSV file or something, obviously you have to process the data as strings and explicitly parse into typed values. But that is not the scenario the OP is describing. – JacquesB Sep 7 '17 at 15:15
  • It could easily be the point I'm describing though; there's nothing special about a string of numbers stored in a csv that demands explicitly declaring the format when parsing and it becomes relevant to the argument I'm making if a newbie reads some answer in SO where the pro doesn't make any effort to explicitly declare date format, leading newbie to assume they don't need to worry about it (or that the db will parse it correctly all the time ) – Caius Jard Sep 7 '17 at 21:36
  • @CaiusJard: I believe these are very different scenarios. When talking about SQL in normal scenarios, I assume columns have the appropriate types - i.e. integer columns are integer type, date columns are data type and so on. If you don't have the correct types in the tables (i.e. store dates as strings) you are in deep trouble and explicit converting date literals in queries will not save you, which is my point. – JacquesB Sep 8 '17 at 7:52
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.

1

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).

  • I agree, though the same problem can be re-forced with parameterized queries, by doing WHERE datecolumn = @dateParameter and then in the front end code, telling the DB driver that @dateParameter is of type varchar, and sticking "01/04/07" in it. The original inspiration for my question is that I suspect anyone who would tell me I'm crazy for doing that to a parameterized query would then, in the same breath, give some one line SO answer that looks like WHERE datecol = 'some string that looks like a date'(and expect a newbie should know it's just a hint/parameterize it to avoid issues) – Caius Jard Sep 6 '17 at 11:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.