Commented by: @asfernandes

Should space separator also be allowed when month is in numeric format (01 01 1900)?

Commented by: @asfernandes

Also, '02oct20' (without spaces) is valid in v3. Should it be?

Commented by: @mrotteveel

Maybe better to ask that on firebird-devel to get more eyes on it. Personally, I'd say: allow spaces in 01 01 1900, but do not accept the version without separators (02oct20), however that could potentially lead to a backwards comptibility issue.

Commented by: @dyemanov

I agree with Mark.

Commented by: @dmitry-starodubov

Our developers usually use formats "DD-MM-YYYY", "YYYY-MM-DD", "DD.MM.YYYY".
So "DD MMM YYYY" is some kind of abberation). But it's used and causes problem to migrate our application to v4.
As for me, I agree with Mark and Dmitry. "DD MM YYYY" looks usable. Date without separators looks weird.

Commented by: @pavel-zotov

Something wrong occurs when delimiter is space and FIRST token (i.e. day number) is greater than 12:

select cast('01 01 2000' as timestamp) from rdb$database;

2000-01-01 00:00:00.0000 -- OK, expected

select cast('12 01 2000' as timestamp) from rdb$database;

2000-12-01 00:00:00.0000 -- OK, expected

select cast('13 01 2000' as timestamp) from rdb$database;

Statement failed, SQLSTATE = 22018
conversion error from string "13 01 2000"

PS
Checked on 4.0.0.2238

PPS.
The same result in FB 2.5 and 3.x, so this is not regression. But anyway parser could be more smarter here.

Commented by: @mrotteveel

@pavel In your example, the first token is taken as the month, not the day (ie American order). This is simply one of the problem with the entirely ambiguous parsing Firebird does. Following the SQL standard to the letter, it should by default only accept something akin to yyyy-MM-dd HH:mm:ss.ffff, all other formats should be handled through CAST( ... as ... FORMAT <format-string>), but that is no longer an option.