Description: Create a table with a column of dates. Enter 15 Oct 1582 and 4 Oct 1582. Execute SQL command "Select * from table". Output will be correct for the first date, but 14 Oct 1582 for second. Steps to Reproduce: 1.Create a table with a column of dates. 2.Enter 15 Oct 1582 and 4 Oct 1582. 3.Execute SQL command "Select * from table". Actual Results: 15 Oct 1582 14 Oct 1582 Expected Results: 15 Oct 1582 4 Oct 1582 Reproducible: Always User Profile Reset: No Additional Info: Version: 6.4.3.2 (x64) Build ID: 747b5d0ebf89f41c860ec2a39efd7cb15b54f2d8 CPU threads: 4; OS: Windows 10.0 Build 18363; UI render: default; VCL: win; Locale: en-US (en_US); UI-Language: en-US Calc: threaded
This is because "Thursday, 14 October 1582" is a proleptic date, which means it is a date that is calculated as a Gregorian date before the date of its adoption (Friday, 15 October 1582). The "Thursday, 4 October 1582" is in fact a Julian date not a Gregorian one. This needs to be further discussed.
The GUI will show the right results. But every date between 1582-10-04 and 1582-10-15 will be shown as 1899-12-30, because the Gregorian counting has been "corrected" 10 days. If you will input data through tools → SQL it will work as expected (without hiding any dates). Don't know why it should work different here … The different behavior between GUI and direct SQL is a bug.
In HSQLDb, I can't enter a date before 15/10/1582 in the table. 04/10/1582 is automatically converted into 24/10/1582, I suppose HSQLDB bug (at least with the old version we use). I tried with MariaDB, I confirm what Robert indicated, SQL and GUI behave differently, in GUI 04/10/1582 stays 04/10/1582, on SQL query, it changes to 14/10/1582 so not specific to embedded Firebird