The result of converting an "invalid" datetime string with julianday() is a
floating point julianday value which corresponds to the normalized datetime.
In your example, date(julianday('2005-02-29')) is '2005-03-01' which is the
correct normalized date.
Therefore, a function which will validate that a string is a valid normalized
date / datetime / time string is the following:
datetime(julianday(x))=datetime(x)
This returns 1 if the x is a valid normal date, datetime, or time and 0 if not;
and, can be used as a check constraint or otherwise.
sqlite> create table mytable
...> (
...> data text collate nocase check (datetime(julianday(data)) =
datetime(data))
...> );
sqlite> insert into mytable values('01:00');
sqlite> insert into mytable values('24:00');
Error: CHECK constraint failed: mytable
sqlite> insert into mytable values('2004-02-29');
sqlite> insert into mytable values('2005-02-29');
Error: CHECK constraint failed: mytable
sqlite> insert into mytable values('0000-01-29');
sqlite> insert into mytable values('-0502-02-29');
Error: CHECK constraint failed: mytable
sqlite> insert into mytable values('-0502-01-29');
Of course, inserting a "numeric" value also passes the test since all strictly
numeric values can be converted to valid datetime strings.
>-----Original Message-----
>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>boun...@sqlite.org] On Behalf Of Philip Bennefall
>Sent: Saturday, 9 August, 2014 14:53
>To: General Discussion of SQLite Database
>Subject: [sqlite] Checking whether a given date is valid
>
>Hi all,
>
>I'm working on a project where I need to verify that a given date
>actually existed (like February 29 in a particular year). I am using
>SqLite to store a lot of data already and I didn't feel like hunting for
>a datetime library online. I figured that there should be a way to use
>SqLite's date functions to check this, and came up with the following
>query:
>
>select 1 where date(julianday('2004-02-29'))=date('2004-02-29');
>
>The above query returns 1 as expected. The following one returns
>nothing, as I would also expect:
>
>select 1 where date(julianday('2005-02-29'))=date('2005-02-29');
>
>Now, my question is simply this. Is the query sound? All the tests I
>have run thus far have given correct results (invalid days of months,
>leap years etc) but are there any pitfalls that I should be aware of? Is
>it safe to rely on the conversion between the floating point julianday
>and the date string being accurate if the date is in fact valid?
>
>Kind regards,
>
>Philip Bennefall
>_______________________________________________
>sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
