On 28-2-2018 10:00, Lester Caine wrote:
On 21/02/18 03:02, Adriano dos Santos Fernandes wrote:
As part of CORE-5750 problems, I found that Firebird considers '12Mar92'
as a valid date (1992-03-12).

Should this be considered a bug, i.e., separators should be necessary in
this case (12-Mar-92, 12/Mar/92, 12.Mar.92)?

I'd consider ANY separator other than a space as an error when using text months. '12Mar92' is at least consistent when spaces and extras are stripped from the data such as '12th March 1992'

Common practice disagrees (although it may vary by geographic region!), dates like 12-MAR-92 are relatively common and are a compromise between human readability and machine parsability.

Technically, the SQL Standard knows only one format, and that is (slightly simplified):

YYYY-MM-DD HH24:MI:SS.FF..+/-TZH:TZHM

or:

<date string> ::=
  <quote> <unquoted date string> <quote>
<time string> ::=
  <quote> <unquoted time string> <quote>
<timestamp string> ::=
  <quote> <unquoted timestamp string> <quote>
<time zone interval> ::=
  <sign> <hours value> <colon> <minutes value>
<date value> ::=
  <years value> <minus sign> <months value> <minus sign> <days value>
<time value> ::=
  <hours value> <colon> <minutes value> <colon> <seconds value>
<unquoted date string> ::=
  <date value>
<unquoted time string> ::=
  <time value> [ <time zone interval> ]
<unquoted timestamp string> ::=
  <unquoted date string> <space> <unquoted time string>

By specification, SQL doesn't know other formats, except through a formatted cast. However, even with a formatted cast, the SQL standard requires a separator which is:

<datetime template delimiter> ::=
  <minus sign>
  | <period>
  | <solidus>
  | <comma>
  | <apostrophe>
  | <semicolon>
  | <colon>
  | <space>

Timezone text is a considerably more complex problem than month text handling especially since even the TZ database can't agree on just what text to use. The only thing that is consistent is Time OFFSET which uses the '-' sign anyway as a flag.

A '+' or '-', not just a '-'.

Mark
--
Mark Rotteveel

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Reply via email to