[yunqa.de] Re: SQLite Date() function returns invalid dates.
- From: Delphi Inspiration <delphi@xxxxxxxx>
- To: yunqa@xxxxxxxxxxxxx
- Date: Mon, 31 May 2010 10:02:08 +0200
At 03:22 31.05.2010, Clyde England wrote:
>I realize this is probably more an issue with core SQLite but I did stumble
>across it when using SQLiteSpy
>
>I had a database that had some suspect SQL dates in it. To track them down I
>was using the following query
>
>Select * from MyTable where Date(MyDate) isNull
>
>For the most part this works just fine. Any invalid formatted dates cause the
>Date() function to return a Null value. However, It would appear this function
>does not pick up "illogical" dates. For example, we all know that April only
>has 30 days in it, but it would appear SQLite thinks otherwise. Try running
>the following simple query in SQLiteSpy - I would expect it to return Null,
>but instead it returns what it *thinks* is a valid date!
>
>Select Date('2010-04-31')
>
>Bug in core Sqlite or a limitation/quirk of the Date() function?
SQLite only tests if the date string is well-formed. If so (like in your
example), it assumes that the YYYY-MM-DD is according to the Gregorian
calendar. It does not check if this is indeed a "valid" date. DISQLite3 and
SQLiteSpy exhibit the exact same behavior.
I can only guess the reasoning behind this, but SQLite being a "lite" DB
engine, application footprint and performance might have been strong
motivations.
Still, you can use the julianday built-in SQL function to test if a date string
is an "invalid" date:
create table date (d text);
insert into date values ('abc');
insert into date values ('2010-02-28');
insert into date values ('2010-02-29');
insert into date values ('2010-02-30');
insert into date values ('2010-02-10');
insert into date values ('2010-04-30');
insert into date values ('2010-04-31');
Select d, ifnull(date(julianday(d)) != d, 1) as is_invalid from date;
Ralf
_______________________________________________
Delphi Inspiration mailing list
yunqa@xxxxxxxxxxxxx
//www.freelists.org/list/yunqa
Other related posts:
