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 [[email protected]]
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
