SQLite Forum

Validating dates for YYYY-MM-DD format
Login

Validating dates for YYYY-MM-DD format

(1) By anonymous on 2020-08-26 00:00:32 [link] [source]

Is there some 'native SQLite3' way for allowing only YYYY-MM-DD formatted dates? (where Y M D are numbers.)

I'm already familiar with the regexp extension:

.load regexp
create table log(dt check (regexp('\d\d\d\d-\d\d-\d\d',dt)));

but I'd rather use something that does not depend on an external extension.

(2) By Mark Lawrence (mark) on 2020-08-26 03:58:46 in reply to 1 [source]

The short version looks like this:

CREATE TABLE a(
    dt TEXT -- Can be NOT NULL if you like
    CONSTRAINT valid_date CHECK(dt IS date(dt,'+0 days'))
);

See [1] for a deeper discussion.

Perhaps this question could go in a FAQ somewhere?

[1] https://marc.info/?l=sqlite-users&m=156578125918594&w=2

(3) By Keith Medcalf (kmedcalf) on 2020-08-26 04:03:10 in reply to 1 [link] [source]

Yes.

create table t
(
  dd text not null constraint dd_invalid_date check(dd == date(dd))
);

(4) By Keith Medcalf (kmedcalf) on 2020-08-26 04:32:09 in reply to 3 [link] [source]

Ooops!

That only works for me because you need to "perform an operation" in order to recompute the YMD when using the default date function (although the iJD is computed, the other valid flags are not reset until you attempt to perform an operation against the iJD). The same would probably apply to the default datetime function as well.

My overridden date/time/datetime functions all a single function that does not require an operation or conversion in order to re-generate the YMDHMSTZ.

(5) By bitxianaiz on 2020-08-30 16:09:46 in reply to 1 [link] [source]

Try this (using CHECK CONSTRAINT instead of regexp)...

—Validate ISO8601 ‘YYYY-MM-DD’ formatted columns, i.e. TEXT column named ‘date_column’
CHECK (LENGTH(date_column) <= 10 AND DATE(date_column, '+0 days') IS date_column)
Accepts valid iso8601 ‘YYYY-MM-DD’ formatted values or NULL

—Validate ISO8601 ‘YYYY-MM-DD hh:mm:ss’ formatted columns, i.e. TEXT column named ‘datetime_column’
CHECK (LENGTH(date_column) <= 19 AND DATETIME(datetime_column, '+0 days') IS datetime_column)
Accepts valid iso8601 ‘YYYY-MM-DD hh:mm:ss’ formatted values or NULL