2

I have been reading all about converting TEXT fields into date formats and ways to use Python to create date objects but my question remains.

My table has a dateTime column that is specified as TEXT, I would like to build a constraint that forces input to be in dateTime format, but as SQLite doesn't do dates (as I would like) I haven't worked out how to do it.

My current ideas: 1. Limit number of characters 2. Set separate attributes for day, month and year and constrain their domains 3. It is silly to do this on the database side just do it in the user interface

I would appreciate your opinions on these or other options. Thanks :)

Lolli
  • 21
  • 3

1 Answers1

4

I've been trying to solve the same issue and the method I came up with was to use the date/time functions to parse the value and check that it hasn't changed. Essentially the following:

CREATE TABLE dts_test (
    dts     TEXT CHECK (dts IS datetime(dts))
);

If the default format is not what you want you can use the strftime or similar to design whatever format you want, but it must be something that the built-in date and time functions can parse.

Parakleta
  • 1,121
  • 10
  • 19