Re: DATETIME_PARSE() too helpful
I recently implemented a data input routine whereby a user enters data into a single-line text field when is then error-checked and possibly converted to another data type based on the value of another field. For instance, if the {Type} field has a value of '[ Y or N ]', the system will accept only 'Y' or 'N', and it stores the value as either 1 or 0, as if it were a checkbox. Similarly, if {Type} is '[ Date ]', it assumes the entered string is in a specified date format and converts it to an Airtable date type using DATETIME_PARSE().
I try to verify entered data is in the proper range and format, alerting when that isn’t the case, but it’s not always easy to do so, given Airtable’s somewhat lackadaisical commitment to its own internal rules. For instance, I’d like to be able to use an ISERROR() check to catch supposed numeric or currency values that contain invalid characters, but a while back I learned such mangled input as VALUE('295gomje78') would effortlessly return 29578: Airtable simply drops the non-numeric characters as if they never existed. Given Airtable deigns to throw an error, I have to kludge together a number of workarounds in an attempt to catch broken data entry.
Similarly, I’d been relying on DATETIME_PARSE() to throw an error when the input string wasn’t formatted to match the function’s template. (I’m using the old U.S. standard, 'M/D/YYYY'.) Testing seemed to show the routine was reasonably finicky about what strings it would eat, with garbage input consistently resulting in #ERROR!.
Today, though, I discovered DATETIME_PARSE(), as well, tries too hard to decipher incorrect input. To an extent, being helpful is fine: I was delighted to discover a 'M/D/YYYY' template worked perfectly well with 'M-D-YYYY' data — even though, strictly speaking, it shouldn’t. But accepting '12 June 2018' as a match for 'M/D/YYYY' is another matter — especially since not only does it fail to throw an error, it muffs parsing the input date.
A quick-and-dirty demo base can be found here — but, frankly, all you need to know can be found in this screen grab:
Only the first of these six dates is in the format DATETIME_PARSE() has been told to expect. Several of the others are valid date formats, just not the one loaded as a template; two of those are misinterpreted.
I can see an argument to be made for DATETIME_PARSE() with no format specifier to be ‘greedy’ and accept any string in a valid date format. (Of course, how would it know if ‘4-1-2018’ was April Fool’s Day or the day after my ex’s January birthday?) With a specifier, though, I’d expect it to throw an error on any incoming string that doesn’t match the format. And I certainly wouldn’t expect it to blithely parse the string incorrectly.
