On Thu Nov 02, 2017 at 10:18:41AM -0400, Richard Hipp wrote: > 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.
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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users