[sqlite] datetime in CHECK

[Cecil Westerhof]

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