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