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

Reply via email to