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.
--
D. Richard Hipp
[email protected]
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
