[samatman]

If there's a risk of properly-formatted but nonetheless invalid dates, like 2024-03-34, one can do this: DATE(date_column, '+0 days') IS date_column). The '+0 days' causes 2024-03-34 to normalize to 2024-04-03 (this is part of the ISO standard!) and therefore the check fails.

Admittedly these sorts of tricks are obscure, if by 'obscure' we mean "you have to feed a search engine a string like 'Validate SQLite datetime' and read some sources". But to reiterate my point slightly differently, the verbosity of these CHECK constraints doesn't indicate that they're doing anything different from a "typed database".

Out of curiosity, I asked ChatGPT, which got the "well formed" version, when I pointed out it would accept 2023-03-34, it gave a correct explanation of what SQLite would do with that date, and suggested `CHECK(date_column = strftime('%Y-%m-%d', date_column))`, which is more satisfying than the other one, and has the same effect. Really gotta keep an eye on the chatbot.