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
sqlite-users mailing list