I think the combo is needed as with just the time function not null constraint you can just insert in plain ol' integer values.
sqlite> select julianday(7); julianday(7) 7.0 sqlite> select datetime(2); datetime(2) -4713-11-26 12:00:00 -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp Sent: Thursday, November 02, 2017 10:19 AM To: SQLite mailing list Subject: Re: [sqlite] [EXTERNAL] SQLite DB - DateTime field values are not consistent On 11/2/17, David Raymond <david.raym...@tomtom.com> 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. -- D. Richard Hipp d...@sqlite.org _______________________________________________ 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
