On Thu Nov 02, 2017 at 10:18:41AM -0400, Richard Hipp wrote:
> On 11/2/17, David Raymond <[email protected]> wrote:
> > For basic level you can use a check constraint
> >
> > create table Table1 (
> > TestDate DATETIME
> > check (TestDate like '____-__-__ __:__:__'
> > and datetime(TestDate) is not null)
> > );
> >
> > That should check that it's in the correct format, and the second part
> > should check that there's no garbage in there.
>
> How about this approach:
>
> CREATE TABLE table1(
> TestDate DATETIME NOT NULL
> CHECK(julianday(TestDate) IS NOT NULL)
> )
>
> All of the date/time functions return a NULL if you give them an
> invalid date/time as input. So checking for a NULL result is a good
> way to validate the date/time. This goes further than the LIKE
> pattern above, as it inhibits nonsense dates such as 'abcd-ef-gh
> ij:kl:mn' which the LIKE pattern would apparently accept.
That approach is also not quite sufficient to ensure that the input is
actually a yyyy-mm-dd hh:mm::ss format, because the SQLite datetime
functions also accept HH:MM, 'now', integers/float...
sqlite> select julianday(2017323.32);
julianday(2017323.32)
---------------------
2017323.32
To be really sure the input conforms to a particular date/time format
you need to round-trip it:
CREATE TABLE Table1(
TestDate DATETIME NOT NULL,
CONSTRAINT valid_datetime CHECK(
TestDate = COALESCE(
datetime( julianday(TestDate) ),
TestDate || x'
)
)
);
--
Mark Lawrence
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
