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

Reply via email to