On 5 August 2010 16:28, K Peters <kpet...@otaksoft.com> 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 ) )
end like theDate||'%' 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
