Yes, either will work. That is if you are using dates only, you can use the
date function. Similarly if you are using times only you can use the time
function. If you might have both, then you need to use the datetime function,
which can validate correct time, date+time, or time only (if date only then the
time is assumed to be 00:00:00, if a time only then the date is assumed to be
2000-01-01).
On Saturday, 9 August, 2014, 18:56, Philip Bennefall <phi...@blastbay.com> said:
>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?
>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
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
