Converting the date from naive (unknown timezone) format to naive utc or naive 
localtime will result in a different date and it will not match for valid dates 
depending on the timezone of your computer and the vagaries of the OS localtime 
conversions, and the particular time-of-day at which the computation is run, 
which will internally vary depending on whether Standard, Daylight, Double 
Daylight, or other times happen to be in effect at that locale.   Attempting to 
add 0 days will simply get back what you put it, round tripped through the 
conversion to and from julian (with the current timezone base).

CHECK (dt IS date(dt, '+0 days')) is where I would go.

create table dt
(
  dt text check (dt IS date(dt, '+0 days'))
);
insert into dt values (null);
insert into dt values ('2019-02-1');
insert into dt values ('2019-02-28');
insert into dt values ('2019-02-29');
insert into dt values ('2019-02-30');
insert into dt values ('2019-02-31');
insert into dt values ('2019-03-01');

Mutatis mutandis for datetime and time.  Note however that things such as the 
localization (timezone offset) and fractional seconds will not be permitted or 
preserved in those cases.

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of no...@null.net
>Sent: Wednesday, 14 August, 2019 06:47
>To: SQLite mailing list
>Subject: Re: [sqlite] specify CHECK clause to date text field
>
>> 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



_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to