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.
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.