VirtualShape - DATE fields are all NULL

[mj10777]

Import of DATE field using VirtualShape does not seem to work correctly.

While working on some wiki pages explaining the different ways to import a Shapefile,
I noticed that DATE fields are always NULL.

https://github.com/geopaparazzi/Spatialite-Tasks-with-Sql-Scripts/wiki/Importing-Shapefiles-Index

The process on how the Shapefile was created (QGIS) is shown on the first page.
The attribute of the Shapefile with ogrinfo and QGIS are shown on the second page.
The third page is indented to show how to use spatialite_gui with the
- Virtual Shapefile
-Load Shapefile
- and a sql-script for the task

-->this page is not yet completed, but at the bottom there is an image showing the attributes in spatialite-gui and QGIS.
    Also there is a download link to the Shapefile-Archiv used, the database created with the sql script and the script itsself.

All 3 versions return a NULL value, so I assume that this is not spatialite_gui specific

Mark

[a.fu...@lqt.it]

On Wed, 23 Jul 2014 00:33:39 -0700 (PDT), mj10777 wrote:
> Import of DATE field using VirtualShape does NOT seem to work
> correctly.
>

I Mark,

as you certainly know the DBF format specifications are rather
vague and uncertain.
A datatype DATE is supported, and the corresponding values are
always expected to be internally encoded as YYYYMMDD (plain text);
no further indications are specified.

Accordingly to all this, while importing any DATE value from a
DBF origin spatialite will apply the following actions:
- does the value length exactly corresponds to 8 bytes ?
if NO, then immediately return NULL
- parse the first 4 bytes (Year): is Year between 1900 and 2400 ?
if NO, then immediately return NULL
- parse the next 2 bytes (Month): is Month between 1 and 12 ?
if NO, then immediately return NULL
- parse the last 2 bytes (Day):
is Month in (1,3,5,7,8,10,12) and is Day between 1 and 31 ?
is Month in (4,6,9,11) and is Day between 1 and 30 ?
is Month=2 (leap year) and is Day between 1 and 29 ?
is Month=2 (non leap year) and is Day between 1 and 28 ?
if NO, then immediately return NULL

if all the above steps successfully pass validation, then a DOUBLE
value corresponding to the Julian Day Number (JDN) will be returned
http://en.wikipedia.org/wiki/Julian_day
JDN is the internal data format directly supported by any SQLite
data/time related SQL function, and consequently seems to be the
most appropriate solution to be adopted.

unhappily, all your dates in the sample shapefile are in the
XIX century, thus failing to pass this validation test:
year >= 1900 && year <= 2400

arbitrarily setting the "origin of the times" as 1900-01-01
is a reasonable solution commonly adopted by many softwares.
the reason is mainly dictated by the incredible messy chaos
caused by the historical adoption of the Gregorian Calendar
in different countries:
- 15 October 1582 on many Catholic countries
- between 1690 and 1753 on many Protestant countries
- after the October Revolution (1917) on Eastern Europe
- during the XX century on many non-Christian countries

just to say, the October Revolution actually happened on
1917-11-07 (Gregorian Calendar), but its name remembers
October simply because that day exactly was 1917-10-25
accordingly to the Julian Calendar then officially
adopted by the Russian Empire.

short conclusion: correctly handling dates is a dates
complex affair; and consequently it's an ugly nightmare
for any computer software.
converting all dates into Julian Day Numbers is a brilliant
and effective solution, but cannot be universally applied
before 1900 because it strictly depends from local historic
events (year when the Gregorian Calendar was officially
adopted in that country).

IMHO the most reasonable solution to handle DATES in SQLite
is the one to always declare TEXT values, then encoding
an ISO-8601 value such as '2014-07-21T17:59:44.625Z'
(and obviously leaving full responsibility to users for
real conformity to Gregorian Calendar).
unhappily this doesn't matches well ar all the floppy specs
of DBF dates ;-)

bye Sandro

[Mark Johnson]

2014-07-23 13:46 GMT+02:00 <a.fu...@lqt.it>:

On Wed, 23 Jul 2014 00:33:39 -0700 (PDT), mj10777 wrote:

Import of DATE field using VirtualShape does NOT seem to work
correctly.

I Mark,

as you certainly know the DBF format specifications are rather
vague and uncertain.
A datatype DATE is supported, and the corresponding values are
always expected to be internally encoded as YYYYMMDD (plain text);
no further indications are specified.

Accordingly to all this, while importing any DATE value from a
DBF origin spatialite will apply the following actions:
- does the value length exactly corresponds to 8 bytes ?
  if NO, then immediately return NULL
- parse the first 4 bytes (Year): is Year between 1900 and 2400 ?
  if NO, then immediately return NULL
- parse the next 2 bytes (Month): is Month between 1 and 12 ?
  if NO, then immediately return NULL
- parse the last 2 bytes (Day):
  is Month in (1,3,5,7,8,10,12) and is Day between 1 and 31 ?
  is Month in (4,6,9,11) and is Day between 1 and 30 ?
  is Month=2 (leap year) and is Day between 1 and 29 ?
  is Month=2 (non leap year) and is Day between 1 and 28 ?
  if NO, then immediately return NULL

But it looks as if Shapefiles (and ogr) do obey the DBASE rules

- ogr returns : valid_sinc (Date) = 1862/05/15

-- this accepts historical dates

These dates are being used in sql date functions (BETWEEN etc), where TIME is not needed

--
You received this message because you are subscribed to a topic in the Google Groups "SpatiaLite Users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/spatialite-users/lvd7uPJ3Big/unsubscribe.
To unsubscribe from this group and all its topics, send an email to spatialite-users+unsubscribe@googlegroups.com.
To post to this group, send email to spatialite-users@googlegroups.com.
Visit this group at http://groups.google.com/group/spatialite-users.
For more options, visit https://groups.google.com/d/optout.

[a.fu...@lqt.it]

On Wed, 23 Jul 2014 13:56:03 +0200, Mark Johnson wrote:
> But it looks as if Shapefiles (and ogr) do obey the DBASE rules
>
> - ogr returns : valid_sinc (Date) = 1862/05/15
>
> -- this accepts historical dates
>

Mark,

the problem isn't so simple as it could appear; there are further
complications we have to take in debt consideration.

SQLite is a DBMS; and on any DBMS there is a reasonable expectation
that arithmetic operations should be always correctly handled,
even in the case of DATE or DATETIME values.
Data/Time SQL functions implemented in SQLite effectively ensure
all this; e.g.:

SELECT strftime('%s','now') - strftime('%s','2014-01-01 00:00:00');
17597097
(how many seconds has passed since now in year 2014)

SELECT date(julianday('now') + 250);
2015-03-30
(date of the next 250th day starting since today)

but for sure we cannot ensure the correctness of such calculations
if we aren't absolutely sure that all dates are expressed accordingly
to the Gregorian Calendar.
this could be reasonably granted for historical events occurred
in Italy, France, Belgium, Spain, Portugal, Austria, Poland and Latin
America; but it becomes an absolutely impossible task for any other
country of the world when we try to go back from XX century.

the DBF own DATE datatype apparently intends full consistency
with the Gregorian Calendar (as intended by any other DBMS), but
can we really trust this information from a floppy file format
lacking any internal validation mechanism and (even more important)
lacking any formal specification about all this ?
I strongly doubt of this. And always assuming that any DBF date
in the past centuries certainly has to be a Gregorian date
seems to be a too much optimistic expectation.
It's certainly safer limiting the validity interval starting
from the begin of XX century ... suppressing a dubious information
seems to be a better option that loading a value of absolutely
uncertain validity.

> These dates are being used in sql date functions (BETWEEN etc), where
> TIME is not needed
>

if this is your scope, you can always get the same identical result
by completely skipping the many hidden traps in the hill-minded DBF
DATE; just encode your dates as plain TEXT strings, e.g. as:
'1813/02/08'
'1743/12/06'
'1521/01/27'
such values will always support plain comparisons (BETWEEN and alike),
but will radically reject any possible bad temptation to perform any
real arithmetic operation on them.

a possible hint to be adopted in some future version of spatialite
------------------------------------------------------------------
converting DBF own dates to Julian Day Numbers is surely the most
wise approach we can adopt in the majority of cases.

anyway, there are other different cases (e.g. your current one)
where DBF dates could be intended under more relaxed expectations
just as plain TEXT strings of the 'YYYY/MM/DD' type.

introducing a further option in the SHP/DBF importer allowing to
freely select the intended DATE interpretation (Julian DOUBLE vs
plain TEXT) could realistically be a good idea.

bye Sandro

[mj10777]

The goal is to import the shapefile (in this case the dbf portion)
- not the interpretation of the data

Looking at the xbase source code
- http://sourceforge.net/projects/xdb/?source=navbar
- xbdate.cpp
CCYYMMDD date

All the date functions use the first 4 characters for the year
- the only check is if year > 0

   /* check the basics */
   if( year == 0 || month < 1 || month > 12 || day < 1 || day > 31 )
      return 0;

int xbDate::IsLeapYear( const char * Date8 ) const
{
   int year;
   year = YearOf( Date8 );
   if(( year % 4 == 0 && year % 100 != 0 ) || year % 400 == 0 )
      return 1;
   else
      return 0;
}

Only when setting a date based on the systemtime
- is the 1900 logic used

I believe it would be wise to tolerate a year (CCYY) between
- 1 and 9999

GDAL: shape2ogr.cpp
- DATETIME is not supported and the only validity check is:

                  if( nYear < 0 || nYear > 9999 )
                  {
                      CPLError(CE_Warning, CPLE_NotSupported,
                               "Year < 0 or > 9999 is not a valid date for shapefile");
                  }

In the Shapefile specification, the only restriction is:
- http://www.esri.com/library/whitepapers/pdfs/shapefile.pdf
-- page 25 (pdf page 29)

The year value in the dBASE header must be the year since 1900

For more information on the dBASE file format, visit the INPRISE Corp. Web site at www.inprise.com
- a site that has been discontinued

My option therefore is:
- if the main creators of dbf (including shape) allow
-- 0 (or 1) to 9999 as a valid year
then spatialite should do the same while importing the data
- the capability of a cpu clock should NOT be taken as a base validity check for historical dates
There will be good reason for people storing historical date in a database
- the interpretation of the result should be left to the user and NOT to the importer

Otherwise a warning is needed:
- ATTENTION: any historical date that a cpu clock cannot read, will be set to NULL!

And that, to be frank, would look a bit silly.

Mark

[Pac]

Hi,

The reference I use for DBF files is Xbase File Format Description. Also, I keep in mind that the ShapeFile description doesn't mention any dBase version, but the minimum common vesion I've found is dBase III (03h in first byte).

In that description, types are located in XBase: Data types.

Cheers,
Paco.

[a.fu...@lqt.it]

cumulative reply :-D

On Thu, 24 Jul 2014 02:48:23 -0700 (PDT), Pac wrote:
> Hi,
>
> The reference I use for DBF files is Xbase File Format Description

> [4]. Also, I keep in mind that the ShapeFile description doesn't

> mention any dBase version, but the minimum common vesion I've found
> is
> dBase III (03h in first byte).
>

> In that description, types are located in XBase: Data types [5].
>

Hi Paco.

the above reference simply states:
"Date in format YYYYMMDD. A date like 0000-00-00 is *NOT* valid."

a very vague definition, omitting many relevant details:
- what's about dates like 1991-02-30 or 2013-13-35 ?
I easily imagine they should be considered invalid, thus
returning a NULL; but the definition itself ignores cases
like these.
- what is the intended validity interval (max/min) ?
yet again, it's gracefully ignored.

we can easily find even more DBF "specs (???)" on the web:

http://devzone.advantagedatabase.com/dz/webhelp/advantage9.0/server1/dbf_field_types_and_specifications.htm
"8-byte date field in the form of CCYYMMDD."
(verbatim: nothing more than this)

http://www.dbase.com/Knowledgebase/INT/db7_file_fmt.htm
"8 bytes - date stored as a string in the format YYYYMMDD."
(same as above)

http://www.okstate.edu/sas/v8/sashtml/accpc/z0214453.htm
"Date: specifies a date value in a format that has numbers and a
character
value to separate the month, day, and year.
The default format is mm/dd/yy, for example, 02/20/95 for February 20,
1995.
Dates in DBF files can be subtracted from one another, with the result
being
the number of days between the two dates. A number (of days) can also
be added
to a date, with the result being a date."

this one seems to be a decisively extravagant definition: accordingly
to mainstream interpretations "02/20/95" should never be an acceptable
DATE value in a DBF.
Anyway it's rather interesting to note that for the first time someone
indirectly introduces the Julian Day Number problem ;-)

----------

all right, coming to a tentative conclusion there are very few doubts:
accordingly to the mainstream interpretation '18160616' should be
considered a *valid* DBF date corresponding to the battle of Waterloo.
exactly as '00790824' corresponds to the Pompeii volcanic eruption.

and this is surely enough to allow us to extract a TEXT value
from a DBF date
Please note: when I say TEXT I exactly intend a value not intended
to be used by any SQL date/time functions and thus definitely excluding
any possible temptation to support arithmetic calculation, as e.g.
computing the difference in day or seconds between two dates.

unhappily the current SpatiaLite implementation supporting DBF dates
always return a DOUBLE value corresponding to a Julian Day Number.
and computing a valid JDN in a conceptually impossible task for any
historical date in the past before 1901-01-01
let's see why in full detail.

Julian Calendar: all years being an exact multiple of 4 are
leap years (266 days, including February 29)

Gregorian Calendar: same as above, with a little correction:
year being and exact multiple of 100 are leap years *only*
if they are exact multiple of 400.

so years 1700, 1800 and 1900 had 366 days on any nation still
following the old Julian rules; but they had just 365 days on
many countries already adopting the Gregorian calendar.

on the other hand years 1300, 1400 and 1500 were universally
considered to be leap years, simply because the Gregorian
correction was initially proposed only in 1581.

> int xbDate::IsLeapYear( const char * Date8 ) const
> {
> int year;
> year = YearOf( Date8 );
> if(( year % 4 == 0 && year % 100 != 0 ) || year % 400 == 0 )
> return 1;
> else
> return 0;
> }
>

and consequently a code snippet like the above one is inaccurate,
because it incorrectly applies the Gregorian rule even before it
was effectively invented :-D

there is a further complication in correctly handling historical
dates: the transition from Julian to Gregorian always implies
"ghost" days.
e.g. in Italy the day immediately following Sunday, 9 December 1582
was Monday, 20 December 1582; days like '15821210' or '15821216'
never existed.

any reasonably robust algorithm pretending to offer full support
for arithmetic operation on DATEs should be expected to be able
to correctly handle such paradoxical cases.
excluding any date before the XX century may probably be a little
bit rough, but certainly is a straightforward effective solution.

> Otherwise a warning is needed:
> - ATTENTION: any historical date that a cpu clock cannot read, will
> be
> set to NULL!
>

sorry, but the JDN problem has nothing to do with CPU clock intrinsics
limitations; please see the above explanation.

the CPU clock (and related operating system support) plays a minor
role, and is related to precision of seconds, not days.
precise time measures doesn't simply depend from leap year; there
are leap seconds as well to take in account :-D

http://en.wikipedia.org/wiki/Leap_second

very short said: the vast majority of days exactly corresponds
to 86,400 seconds; but here and there are few days of 86,399 or
86,401 seconds, so to reconciliate the time to the actual orbital
parameters of planet Earth.

at least on Linux systems the internal clock is expected to be
precisely valid only between 1970 and 2037; outside this interval
you cannot reasonably expect seconds to be correctly computed due
to "leap seconds" issues.
but this one is a different problem from the one of computing JDN
values, that simply depends on leap years and not on leap seconds.

On Wed, 23 Jul 2014 20:49:53 -0700 (PDT), mj10777 wrote:
> My option therefore is:
> - if the main creators of dbf (including shape) allow
> -- 0 (or 1) to 9999 as a valid year
> then spatialite should do the same while importing the data
> - the capability of a cpu clock should NOT be taken as a base
> validity
> check for historical dates
> There will be good reason for people storing historical date in a
> database
> - the interpretation of the result should be left to the user and NOT
> to the importer
>

Mark,

I fully agree with your global conclusions.

- the current implementation supported by SpatiaLite is surely
useful for "modern" dates (certainly of the Gregorian type)
- but isn't able to reasonably handle doubtful "historical
dates" of unclear/uncertain attribution; anyway historical
dates are interesting as well, and some kind of support
is expected to be implemented.

So we absolutely need to expand the current implementation,
carefully avoiding to break any backward compatibility.
this isn't an impossible task; as I already proposed yesterday
we simply have to expand the DBF/SHP import functions introducing
a furher argument, so to allow the users to freely choose (under
their full responsibility) between a "julian" and a "text"
interpretation for DBF Dates.

Today isn't exactly the best time for introduce a major change
like this (potentially introducing some hazards of possible
regressions) because we are currently in the final steps of
releasing 4.2.0, and this prudentially imposes a "code freeze".

anyway this new feature will be implemented with a very high
priority immediately after releasing 4.2.0

bye Sandro

[Pac]

Hi Sandro,

I agree with you: telling only the pattern in a date is only part of a full description, even assuming that a DBF was developed with an "en-us" culture in mind.

For that reason the use of those "specs" should be as a general guide, because in real life you have to deal not only with "specs" but with "implementations": shapefiles names (and hence DBF filenames) not following 8.3 naming convention, numeric (N type) values in DBF with comma-separated decimals or in scientific notation (like 1e6), and so on.

And for dates, I agree with the user having the option to choose its interpretation.

Bye,
Paco.

[Mark Johnson]

2014-07-25 10:09 GMT+02:00 Pac <d.paco...@gmail.com>:

Hi Sandro,

I agree with you: telling only the pattern in a date is only part of a full description, even assuming that a DBF was developed with an "en-us" culture in mind.

Internally (i.e. inside the .dbf files) the date is stored as CCYYMMDD

- which is basicly the ISO Date format (which possibly did not exist then) without the '-'

-- this can be imported as 'CCYY-MM-DD'

All that is needed at the moment is the replacement of :
- Year between 1900 and 2400 ?

with
- Year between 0001 and 9999 ?

For that reason the use of those "specs" should be as a general guide, because in real life you have to deal not only with "specs" but with "implementations": shapefiles names (and hence DBF filenames) not following 8.3 naming convention, numeric (N type) values in DBF with comma-separated decimals or in scientific notation (like 1e6), and so on.

And for dates, I agree with the user having the option to choose its interpretation.

Yes, I aggree

--

You received this message because you are subscribed to a topic in the Google Groups "SpatiaLite Users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/spatialite-users/lvd7uPJ3Big/unsubscribe.

To unsubscribe from this group and all its topics, send an email to spatialite-use...@googlegroups.com.
To post to this group, send email to spatiali...@googlegroups.com.

[Andrea Peri]

Hi Alessandro .
I guess spatialite should only  detect if the text is a compatible date string
But spaitalite should not try to understand the date what mean.

So the check for leap year also is really unuseful.

there is many software that don't check this and so allow the insert of
date 30/02/2014.

This is of course a wrong date, but why lost what was wrote changing it to NULL ?

As use case:
If I could load it on spatialite I could of course do a select to find them and resolve updating their value.

If it is NULLED it is definitively lost.
And no update could be done.

Regards,

Andrea.

[Mark Johnson]

2014-07-25 10:21 GMT+02:00 Mark Johnson <mj1...@googlemail.com>:

2014-07-25 10:09 GMT+02:00 Pac <d.paco...@gmail.com>:

Hi Sandro,

I agree with you: telling only the pattern in a date is only part of a full description, even assuming that a DBF was developed with an "en-us" culture in mind.

Internally (i.e. inside the .dbf files) the date is stored as CCYYMMDD

- which is basicly the ISO Date format (which possibly did not exist then) without the '-'

-- this can be imported as 'CCYY-MM-DD'

All that is needed at the moment is the replacement of :

- Year between 1900 and 2400 ?

with
- Year between 0001 and 9999 ?

I should have added:

- this is only for the pure import function

[a.fu...@lqt.it]

On Fri, 25 Jul 2014 02:14:21 -0700 (PDT), Andrea Peri wrote:
> Hi Alessandro .
> I guess spatialite should only detect if the text is a compatible
> date
> string
> But spaitalite should not try to understand the date what mean.
>
> So the check for leap year also is really unuseful.
>
> there is many software that don't check this and so allow the insert
> of
> date 30/02/2014.
>
> This is of course a wrong date, but why lost what was wrote changing
> it to NULL ?
>
> As use case:
> If I could load it on spatialite I could of course do a select to
> find
> them and resolve updating their value.
>
> If it is NULLED it is definitively lost.
> And no update could be done.
>

Hi Andrea,

I fully agree with your considerations; the DBF DATE specs are
absolutely
flimsy and on the wild there are surely lots of DBF/SHP files
containing
DATE values formatted accordingly to most extravagant interpretations.

So we should be always prepared to encounter some "crazy" value while
importing; and effectively returning a NULL in this case prevents any
further corrective action or validation/post-processing task.

Handling invalid DATE values is practically impossible when adopting
the
current DOUBLE (Julian Day Number) approach.
But will become an absolutely easy task when adopting the suggested
alternative TEXT approach.

few basic rules to be applied by the future TEXT implementation:

- never attempt to check the year, month or day
- just support the CCYYMMDD rule in the most permissive way
- always faithfully preserve all input values, including the most
obviously malformed ones

just two practical examples:

1) the input DBF DATE value is '19801335'
such value formally conforms to the CCYYMMDD rule (8 digits), and
consequently will be imported into the DB as '1980/13/35'

2) the input DBF DATE value is 'alfabeta'
such value doesn't match at all the CCYYMMDD rule and will be
imported exactly "as is" (without any manipulation).

bye Sandro

[Andrea Peri]

I undersyand you point of view.
To try to detect the most probable value. But, why in the text version import you put the / char ?

I guess a more sure solutipn is to avoid to put the / char or allow the user the choice to give him the right mask.

A.

[mj10777]

On Saturday, 26 July 2014 18:49:24 UTC+2, Andrea Peri wrote:

I undersyand you point of view.
To try to detect the most probable value. But, why in the text version import you put the  / char ?

This has, most probably, traditional reason only.
- at the time DBASE was developed, Internationalization was unknow
-- only the second ASCII version supported an non-english letters (the reason why ASCII string are not really sortable)

DBASE itself (i.e. the DBASE Interpreter language) only supported '/' when printing dates
- showing MM/DD/YYY, which was only used in the USA (Britain used DD/MM//YY)
--> I always hate importing data where it was not clear whether it came from the USA or UK if the DD were all under '13'
-- that there were cultures that use '.' in dates was completely unknown

This is, BTW, why ISO defined '-' (YYYY-MM-DD) as the separator, using a symbol that was (to my knowledge) not used by anybody else
- so in contention with .dbf, '/' is still used
-- gdal/ogr prints with ogrinfo shapefile date-entries with '/'

In this way it is a sort of 'secret warning': this could be anything

I guess a more sure solution is to avoid to put the / char or allow the user the choice to give him the right mask.

A placeholder is a must
- or what is '11121211' ?
-- which is the year, month or day?

A.

[a.fu...@lqt.it]

Andrea,

I fully agree: when using the TEXT interpretation always preserving
the input value exactly "as is" (i.e. avoiding to introduce
any internal delimiter, slash, hyphen or whatever else) seems
to be a very brilliant suggestion.

Because this way the user will always have full control about
his/her preferred interpretation of DATE values.
just few practical examples:

CREATE TABLE test (date TEXT);
INSERT INTO test VALUES ('20140726');
INSERT INTO test VALUES ('18140618');
INSERT INTO test VALUES ('00790824');


SELECT printf('%s-%s-%s', substr(date, 1, 4),
substr(date, 5, 2), substr(date, 7, 2))
FROM test;
----------------------
2014-07-26
1814-06-18
0079-08-24


SELECT printf('%s/%s/%s', substr(date, 7, 2),
substr(date, 5, 2), substr(date, 1, 4))
FROM test;
-----------------------
26/07/2014
18/06/1814
24/08/0079


SELECT printf('%s %s %s', substr(date, 7, 2),
CASE
WHEN substr(date, 5, 2) = '01' THEN 'January'
WHEN substr(date, 5, 2) = '02' THEN 'Febryary'
WHEN substr(date, 5, 2) = '03' THEN 'March'
WHEN substr(date, 5, 2) = '04' THEN 'April'
WHEN substr(date, 5, 2) = '05' THEN 'May'
WHEN substr(date, 5, 2) = '06' THEN 'June'
WHEN substr(date, 5, 2) = '07' THEN 'July'
WHEN substr(date, 5, 2) = '08' THEN 'August'
WHEN substr(date, 5, 2) = '09' THEN 'September'
WHEN substr(date, 5, 2) = '10' THEN 'October'
WHEN substr(date, 5, 2) = '11' THEN 'November'
WHEN substr(date, 5, 2) = '12' THEN 'December'
END,
substr(date, 1, 4))
FROM test;
-----------------------
26 July 2014
18 June 1814
24 August 0079


SELECT printf('%s %s, %s', CASE
WHEN substr(date, 5, 2) = '01' THEN 'Jan'
WHEN substr(date, 5, 2) = '02' THEN 'Feb'
WHEN substr(date, 5, 2) = '03' THEN 'Mar'
WHEN substr(date, 5, 2) = '04' THEN 'Apr'
WHEN substr(date, 5, 2) = '05' THEN 'May'
WHEN substr(date, 5, 2) = '06' THEN 'Jun'
WHEN substr(date, 5, 2) = '07' THEN 'Jul'
WHEN substr(date, 5, 2) = '08' THEN 'Aug'
WHEN substr(date, 5, 2) = '09' THEN 'Sep'
WHEN substr(date, 5, 2) = '10' THEN 'Oct'
WHEN substr(date, 5, 2) = '11' THEN 'Nov'
WHEN substr(date, 5, 2) = '12' THEN 'Dec'
END,
substr(date, 7, 2), substr(date, 1, 4))
FROM test;
-----------------------
Jul 26, 2014
Jun 18, 1814
Aug 24, 0079


SQLite nicely supports several SQL functions well
fitted to reformat any DATE in many possible ways;
and transforming the above queries into an UPDATE
is a trivial task. Exactly as is an easy task
transforming such queries into VIEWs.
Seems to be a very promising and flexible approach.

bye Sandro

[sandro furieri]

Follow up:
-----------------

the code implementing this patch has already been committed
into the Fossil repositories.

1) now the library API will support both interpretation
   (JulianDayNumber / plaintext) when importing DATE-type
   fields from an external DBF file.
   This obviously applies to Shapefiles as well.
   
2) both ".loadshp" and ".loaddbf" dot-macros supported by
   the spatialite CLI tool will now accept a further bool
   argument (1/0) allowing to choose the preferred
   interpretation.
   the default value is "0" (JulianDay) and exactly
   corresponds to the traditional behaviour as before.
   
3) the same if for both VirtualDBF and VirtualSHP

4) and finally the Dialog Boxes available on spatialite_gui
   and supporting DBF and Shapefile import will now support
   this further option (see attached figure)
   
bye Sandro