[sqlite] Checks with dates

[Cecil Westerhof]

2016-04-15 2:29 GMT+02:00 Keith Medcalf <kmedcalf at dessus.com>:

>
> create table x
> (
>   ds text not null check(date(ds) is not null),
>   de text not null check(date(de) is not null),
>   check(ds < de)
> );
> ??
> ??
> insert into x values ('2016-04-15', '2016-04-13');
> insert into x values ('2016-04-15', '2016-04-17');
> insert into x values ('2016-04-15', '2016-04-32');
>
> The constraint on the column values is that date() is not null (ie, a
> valid date)
>

?Almost, but the dates are allowed to be NULL. But this led me to:
CREATE TABLE x (
    ds TEXT CHECK(ds IS NULL OR COALESCE(DATE(ds) == ds, 0)),
    de TEXT CHECK(de IS NULL OR COALESCE(DATE(de) == de, 0)),
    CHECK(ds IS NULL OR de IS NULL OR ds < de)
)

And this does what I want.

Thanks.

-- 
Cecil Westerhof