Re: [sqlite] Check for valid dates?

[Simon Davies]

On 5 August 2010 16:28, K Peters  wrote:
> Hi all,
>
> is there a way to check for valid ISO 8601 dates?

Something like:

sqlite> select
case theDate like '%T%' when 1 then
replace( datetime( julianday( theDate ) ),' ','T') else
datetime( julianday( theDate ) )
endliketheDate||'%' is not null as status,
theDate
from (
select '2001-12-12 14:10:12' as theDate union
select '2001-1-31' union
select '2002-12-30T14:40' union
select '2002-12-30 14:30' union
select '2002-12-30 14:2' union
select '202-12-30' union
select '2002-12-30T14:30:22.22' union
select '2002-12-30 14:30.22.1z' union
select '2002-12-30T14.30.22.1e' union
select '2002-12-30 14:30:22Z' ) order by status;
0|2001-1-31
0|2002-12-30 14:2
0|2002-12-30 14:30.22.1z
0|2002-12-30T14.30.22.1e
0|202-12-30
1|2001-12-12 14:10:12
1|2002-12-30 14:30
1|2002-12-30 14:30:22Z
1|2002-12-30T14:30:22.22
1|2002-12-30T14:40

The sqlite date functions use a subset of IS0-8601 date and time
formats, see http://www.sqlite.org/lang_datefunc.html

Date with week number and ordinal dates do not appear to be handled as input.
Also, invalid dates (eg. 30th Feb) are not detected.

>
> Thanks
> Kai

Regards,
Simon
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Check for valid dates?

[Simon Slavin]

On 5 Aug 2010, at 4:28pm, K Peters wrote:

> is there a way to check for valid ISO 8601 dates?

Use a date library to turn your date into a number (e.g. epoch, Julian day), 
then turn it back into a string again.  If you get back the same string you 
start with, it's valid.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Check for valid dates?

[K Peters]

Hi all,

is there a way to check for valid ISO 8601 dates?

Thanks
Kai
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users