Leap day weirdness with dates that are out of range

[dgtrapeze]

I originally sent this to the [email protected] mailing list but re-read your support page and it says to use github instead for issues. The original post was https://www.postgresql.org/message-id/DU0P191MB2082B40B00EE9A8CEE50F16C865F2%40DU0P191MB2082.EURP191.PROD.OUTLOOK.COM

If a postgresql database table contains a date with more than 4 digits in the year, it appears that the postgresql ODBC driver does something weird:

• It takes the first 4 digits of the year
• It then uses todays day and month

It then returns this date as the value of the column. The client software then tries to interpret this date into its native date type which, when the current date is the 29th Feb fails 75% of the time with a data conversion exception due to the resulting date not existing.

For example, if the date in the database is 20238-08-01 (eg it looks the original user did a keying error and put the month into the year field), this gets returned by the ODBC driver as 2023-02-29. The client application then 'fails' in some way as this is not a valid date.

We've been a bit busy manually trying to find and change invalid dates to fully work it through but I did a simple test using Excel as the ODBC client to prove what was happening. If the first 4 digits of a 5 digit year date happen to be a leap year, all is well. But if they are not a leap year, something bad happens.

Having a quick look at the driver code, from what I can see the issue is in copy_and_convert_field

I assume the parsing of the postgresql date using the scanf in the lines

                            case PG_TYPE_DATE:
                                            sscanf(value, "%4d-%2d-%2d", &std_time.y, &std_time.m, &std_time.d);

Reads the 1st 4 digits of the year but the rest just fails and m and d are left set to zero.

Then later on the code does

                                                                            /*
                                                                            * Initialize date in case conversion destination
                                                                            * expects date part from this source time data.
                                                                            * A value may be partially set here, so do some
                                                                            * sanity checks on the existing values before
                                                                            * setting them.
                                                                            */
                                                                            tim = SC_get_localtime(stmt);
                                                                            if (std_time.m == 0)
                                                                                            std_time.m = tim->tm_mon + 1;
                                                                            if (std_time.d == 0)
                                                                                            std_time.d = tim->tm_mday;
                                                                            if (std_time.y == 0)
                                                                                            std_time.y = tim->tm_year + 1900;

Which sets any date fields which are zero to their values from 'today'.

For us, it would have been better if the ODBC driver returned either an error of some sort telling the user about the invalid date, or returned some date which was 'valid' but logically represents an 'out of range' value such as 9999-12-31 rather than return a date that doesn't actually exist. With the current behaviour, some applications seemed to show an 'Error' in the field while others have just crashed with an exception when they come across the 'invalid' date being returned.

For the rest of the days of the year, the user gets given a 'valid' (although somewhat random) date and just assumes that is the data in the database! They have no way to see the 'real' data to know otherwise.

29 Feb 2020 was a Saturday and so I guess very few users were querying the database and we didn't notice the issue. 29 Feb 2016 is so long ago no one around here remembers if it was mayhem - but 29 Feb 2024 has been busy for us!