Thank you. I did not notice loss of fractional seconds. I now see in the manual that datetime() is equivalent to strftime('%Y-%m-%d %H:%M:%S', ...), with capital "S" rather than lower "f" at the end that I expected.
Roman ________________________________________ From: sqlite-users-bounces at mailinglists.sqlite.org [sqlite-users-bounces at mailinglists.sqlite.org] on behalf of Cecil Westerhof [cldwester...@gmail.com] Sent: Wednesday, May 04, 2016 5:37 PM To: SQLite mailing list Subject: Re: [sqlite] datetime in CHECK 2016-05-04 22:43 GMT+02:00 Roman Fleysher <roman.fleysher at einstein.yu.edu>: > Dear SQLiters, > > I am trying to use CHECK constraint is column of a table to enforce > datetime format and this works: > > AcquisitionDate TEXT > ?? > CHECK (AcquisitionDate IS date(AcquisitionDate)) > > when I insert '2015-08-10'. But this > > AcquisitionDateTime TEXT CHECK (AcquisitionDateTime IS > datetime(AcquisitionDateTime)) > > when I insert '2015-08-10T17:19:37.670' or '2015-08-10 17:19:37.670' > fails. Why? ?That is because datetime is not more precise as seconds. When you execute: SELECT datetime('2015-08-10T17:19:37.670') you get: 2015-08-10 17:19:37 How to do it properly? > ?Do not enter the part after seconds (.670). If that is important, executing: SELECT strftime('%Y-%m-%d %H:%M:%f', '2015-08-10T17:19:37.670') gives: 2015-08-10 17:19:37.670 So you could use: ? ? ?CHECK (AcquisitionDate IS strftime('%Y-%m-%d %H:%M:%f', AcquisitionDate))? -- Cecil Westerhof _______________________________________________ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users