Garbage in, garbage out I guess?

You could try select date('2023-02-30','0 days');. That fources normalization of the date.

Yes, normalizing works, but most bad dates return null, and I think this should also return null for consistency, but I would not consider this an egregious problem.

try this:

select date(julianday('2023-02-29'))

interesting ha.

As of earlier today there's a commit to ensure that the YYYY-MM-DD input to date and time functions has been normalized prior to returning a result.

sqlite> select date('2023-02-30');
2023-03-02

Hmmm... no sanity check?

What does select date('2022-02-365') return with this sort of normalization?

I'm guessing something like '2024-02-01'

Shouldn't an invalid date be recognized as invalid rather than forced to a valid but possibly incorrect value.

I'm guessing something like '2024-02-01'

It's NULL. Any day number of 32 or higher is treated as outright invalid (NULL). Any day number of 31 or less will, for months with fewer days, wrap around to the next month.

Shouldn't an invalid date be recognized as invalid rather than forced to a valid but possibly incorrect value.

Arguably so, but backwards compatibility constraints restrict the level of changes which can be made to the date/time functions.

I suggest the logic of duckdb

D select date'2023-02-29';
Error: Conversion Error: date field value out of range: "2023-02-29", expected format is (YYYY-MM-DD)
D select date'2023-02-27';
┌────────────────────────────┐
│ CAST('2023-02-27' AS DATE) │
│            date            │
├────────────────────────────┤
│ 2023-02-27                 │
└────────────────────────────┘

...That fources normalization of the date

It's hardly a normalization, more like an "autocorrect".

The invalid date should be flagged with a warning at least or just return NULL to be consistent with date('2023-01-32','0 days').

I suppose if it was considered important enough, one could add a pragma to enforce more stringent rules for date parsing. Is it worth the trouble? My guess is no, but the possibility exists.

...break an unknown number of applications

Flagging with a warning does not equate "breaking". More so, it's unknown if the "autocorrect" behavior was yielding the expected value in the affected applications.

Ok then genius, how do you propose "flagging with a warning" to proceed in a way that does not break legacy applications? Especially considering that you do not know if and how many applications can deal with or even rely upon the current behaviour.

I'd take it as your genuinely trying to find solution to this issue and not wasting words in pointless arguments.

The warning can be issued with utf8_printf(stderr, "Warning: ...") or similar just as for other cases needing to signal a warning in SQLite.

The warning can be issued with utf8_printf(stderr, "Warning: ...") or similar just as for other cases needing to signal a warning in SQLite.

What you seem to misapprehend is that where the error occurs is in the SQLite library. There is no warning possible. You assume that it can just write to stderr. That might be fine for an application, especially a console app. It would be exceedingly presumptuous and likely inappropriate or impossible for the library. In a windowed application, stderr may not be available. And it is not the library's role to use side-channels.

I'd take it as your genuinely trying to find solution to this issue and not wasting words in pointless arguments.

Clearly not pointless. The "genius" may have been too much.

The function call in question (utf8_printf()) is only used in shell.c, so one may presume that the anonymous poster up-thread is asking for the shell to diagnose this, not SQLite proper.

How one is meant to do that when it isn't the shell parsing and interpreting the dates is a different question.

Something I haven't seen come up in this thread is, what does the standard say? How do other SQL engines handle this? Is SQLite an outlier here, or is it doing what everyone else does with this GIGO case?

There is no warning possible

SQLite does have a log available and does issue warnings that way. For example

select "hello";

has the log handler called with a code of SQLITE_WARNING and message of

double-quoted string literal: "hello"

I think it is pretty simple - database engine must have built-in check to validate date. This is, I think, how it works in any other database. For example, this is mysql (MariaDB) response: select date('2023-02-29'); +--------------------+ | date('2023-02-29') | +--------------------+ | NULL | +--------------------+ 1 row in set, 1 warning (0.000 sec)

MariaDB select date('2024-02-29'); +--------------------+ | date('2024-02-29') | +--------------------+ | 2024-02-29 |

I think, SQLite treats February as ordinary month, thinking it might have maximum 31 days. And, unfortunately, SQLite engine is not aware of existence of leap years.

And, unfortunately, SQLite engine is not aware of existence of leap years.

Do you have any evidence to support that assertion? I believe it is false.

@larrybr - Did you have a chance to read my post? according to what sqlite3 gave back to me, it treats February as a ordinary month and 'thinks' it has maximum 31 days in it. for 2023-02-29 it happily returns this date, meaning from sqlite3 perspectives - it exists in 2023. It does the same for 2024-02-29, which is correct. but it is correct answer not because sqlite validated it - it is because it is under impression that there are 31 days in February sqlite> select date('2023-02-29'); 2023-02-29 sqlite> select date('2023-02-30'); 2023-02-30 sqlite> select date('2023-02-31'); 2023-02-31 sqlite> select date('2023-02-32');

sqlite>

As you can see the only date it refused to reply was 2023-02-32. Am I missing/do not understand something here?

Actually... after simple check it is even more ridiculous - turned out, sqlite3 date function is under impression that all months in a year might have maximum 31 days... January, February, March, April... so on. sqlite> select date('2023-02-31'); 2023-02-31 sqlite> select date('2023-03-31'); 2023-03-31 sqlite> select date('2023-04-31'); 2023-04-31 sqlite> select date('2023-05-31'); 2023-05-31 sqlite> select date('2023-06-31'); 2023-06-31 sqlite> select date('2023-07-31'); 2023-07-31 sqlite> select date('2023-08-31'); 2023-08-31 sqlite> select date('2023-09-31'); 2023-09-31 sqlite> select date('2023-10-31'); 2023-10-31 sqlite> select date('2023-11-31'); 2023-11-31 sqlite> select date('2023-12-31'); 2023-12-31 sqlite>

Well, you've got three options: update when it's available since this issue has already been addressed, or do date(whatever, '0 days') to force SQLite to normalize your bad input, or stop putting in bad dates.

@larrybr - Did you have a chance to read my post?

Yes, that's why I replied, asking for proof of your assertion, "SQLite engine is not aware of existence of leap years."

I wonder how a logical argument to that effect would survive this: sqlite> SELECT timediff('2023-02-28','2023-03-01'); -0000-00-01 00:00:00.000 sqlite> SELECT timediff('2024-02-28','2024-03-01'); -0000-00-02 00:00:00.000 sqlite> , which is a screen scrape showing the contrary.

... As you can see the only date it refused to reply was 2023-02-32. Am I missing/do not understand something here?

Yes. You have confounded the library's treatment of invalid dates with its treatment of valid dates. Your assertion just disproved is what is known as a "weak inference". Such an inference is not necessarily false, but is not logically proven by the facts upon which it is based.

If you had said "SQLite does not apply its knowledge of leap years in every context in which it might be applicable.", then I would not have denied it. That is a logically correct deduction.

Looking for the truth in the code:

• date.c:dateFunc() in question does not appear to do any month-specific validation in case of YMD dates;
• date.c:parseYyyyMmDd() does enforce the format but not the validity of the actual year/month/day combination;
• the year/month/day set is converted into Julian date;
• the resulting date is simply a re-formatted YMD from the parsed values.

Meanwhile the timediff() is likely done using the Julian or epoch representation.

Technically, it's possible to the "wrap around" in the date() function too by back-converting from the Julian date. This should return a valid date.

However this would only further cement this "silent-autocorrect" behavior.

...SQLite is aware of leap years

The only parts "aware" of the leap years are the ones that deal with Julian date conversion. That's also the main reason the "wrap around" actually takes place in the SQLite handling of the invalid dates.

Specifically, the invalid YMD is first internally converted into "wrapped around" Julian date. The extra days are simply added to the resulting Julian days. Then any subsequent manipulation of such date will make use of the this pre-computed Julian date.

The manipulation result in Julian date form is then converted back into the YMD, see date.c:computeYMD().

Thus, for the least degree of consistency, such invalid YMD date should be explicitly converted into "wrapped around" YMD form and returned from the date() function. Instead of implicitly keeping a "wrapped around" Julian date, yet returning the invalid YMD date.

Still, a proper warning should be flagged in one way or another. Perhaps some additional condition value could be returned from the API and checked by the caller or some other way to signal the implicit "normalization".

Either way no application would willingly choose to keep explicitly invalid dates in the database. Whether in such invalid date instances the applications would keep and rely upon the "wrapped around" values, it is reasonable to expect that.

Preferably, such active normalization behavior by SQLite engine should be controlled with pragmas or options.