> It seems the date function does not check that the date is valid,
> only the format.
I've run into the same issue. Don't remember if it has been raised on
the list, but I have a vague memory that it fell into the WONTFIX
category :-(
> 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.
The following might be instructive:
.version
.width 10 10 2 15 2 21 2
WITH
x(dt)
AS (
VALUES
(NULL),
('2019-02-00'),
('2019-02-01'),
('2019-02-1'),
('2019-02-29'),
('2019-02-30'),
('2019-02-31'),
('2019-02-32')
)
SELECT
x.dt AS "str",
date(x.dt) AS "date(str)",
x.dt IS date(x.dt) AS "IS",
date(x.dt,'utc') AS "date(str,'utc')",
x.dt IS date(x.dt,'utc') AS "IS",
date(x.dt,'localtime') AS "date(str,'localtime')",
x.dt IS date(x.dt,'localtime') AS "IS"
FROM
x
;
-- Output
SQLite 3.22.0 2017-11-27 17:56:14
465350e55ddaf30cfba7874653301de7238a9dc2bc5e1f800fc95de9360679f6
str date(str) IS date(str,'utc') IS date(str,'localtime') IS
---------- ---------- -- --------------- -- --------------------- --
NULL NULL 1 NULL 1 NULL 1
2019-02-00 NULL 0 NULL 0 NULL 0
2019-02-01 2019-02-01 1 2019-01-31 0 2019-02-01 1
2019-02-1 NULL 0 NULL 0 NULL 0
2019-02-29 2019-02-29 1 2019-02-28 0 2019-03-01 0
2019-02-30 2019-02-30 1 2019-03-01 0 2019-03-02 0
2019-02-31 2019-02-31 1 2019-03-02 0 2019-03-03 0
2019-02-32 NULL 0 NULL 0 NULL 0
I think the above means you can compare the original value with the
localtime equivalent:
CREATE TABLE table_a(
dt TEXT -- NOT NULL if you like
CONSTRAINT valid_date CHECK(dt IS date(dt,'localtime'))
);
Regards,
Mark
--
Mark Lawrence
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
