Hi Keith,

Thanks for the explanation. From what I can see in your example, the only difference from mine is that I used date(x) and you used datetime(x). If I am understanding things correctly, this would give the same behavior if I am only concerned about dates? In this case I don't care about the time of day; just that the date is valid. So based on your description I gather that I'm doing it correctly in my example? I hope?

Kind regards,

Philip Bennefall
On 8/10/2014 12:00 AM, Keith Medcalf wrote:
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

Reply via email to