3 ms·
Heh, I haven't seen that particular datetime constraint before, thanks for that!
by asa400 3y ago
Heh, I haven't seen that particular datetime constraint before, thanks for that!
- samatman 3y agoIf 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.