On 12/8/19 10:59 PM, Martin wrote:
> sqlite> .version
> SQLite 3.29.0 2019-07-10 17:32:03
> fc82b73eaac8b36950e527f12c4b5dc1e147e6f4ad2217ae43ad82882a88bfa6
> zlib version 1.2.11
> clang-10.0.1
> sqlite> select date('2019-02-00'); -- null
>
> sqlite> select date('2019-02-01'); -- ok
> 2019-02-01
> sqlite> select date('2019-02-1' ); -- null
>
> sqlite> select date('2019-02-29'); -- not a leap year
> 2019-02-29
> sqlite> select date('2019-02-31'); -- ?
> 2019-02-31
> sqlite> select date('2019-02-32'); -- null
>
> sqlite> .quit
>
> It seems the date function does not check that the date is valid, only the
> format.
> Regardless of month it accepts day numbers from '01' to '31'.
Here's a Dirty Little Secret: All the SQLite date functions are centered
around strftime(), which is not implemented in a strictly correct sense
in *every* Unix-like platform I've seen. SQLite at least does a simple
range check on the day-of-month; using your platform's strftime() C
function, the equivalent of 2019-02-1234 is perfectly legal, and gives
you the date that's 1233 days after Feb 1 2019.
> Consequently, I would appreciate any advice on the preferred way to specify a
> CREATE TABLE .. CHECK clause
> to guard inserting a yyyy-mm-dd date into a text field.
Off the top of my head, simply "round-trip" the date string to Julian
days and back again, then compare the two dates. Of course, you should
first check that the Julian conversion succeeded.
=====
$ cat test.sql
CREATE TABLE t(a TEXT CONSTRAINT valid_date CHECK (
strftime('%J', a) IS NOT NULL AND a = date(strftime('%J', a)))
);
INSERT INTO t VALUES('2019-02-00');
INSERT INTO t VALUES('2019-02-01');
INSERT INTO t VALUES('2019-02-1');
INSERT INTO t VALUES('2019-02-28');
INSERT INTO t VALUES('2019-02-29');
INSERT INTO t VALUES('2019-02-30');
INSERT INTO t VALUES('2019-02-31');
INSERT INTO t VALUES('2019-02-32');
SELECT * FROM t;
$ sqlite3 < test.sql
Error: near line 2: CHECK constraint failed: valid_date
Error: near line 4: CHECK constraint failed: valid_date
Error: near line 6: CHECK constraint failed: valid_date
Error: near line 7: CHECK constraint failed: valid_date
Error: near line 8: CHECK constraint failed: valid_date
Error: near line 9: CHECK constraint failed: valid_date
2019-02-01
2019-02-28
=====
--
Best Regards,
Adrian
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
