[GNC] How can I make GnuCash store all dates in only 1 format?

[John Ralls]

> On Jul 28, 2019, at 2:53 AM, Mark Debian via gnucash-user <gnucash-user at gnucash.org> wrote:
> 
> I have a script I use to massage data from GnuCash via the sqlite backend.
> 
> It looks like the format that dates are stored in the database backend has changed over time and this is causing me problems.  When I look at the transactions table I have dates in the following formats:YYYYmmddHHMMSSYYYY-mm-dd HH:MM:SS
> Unfortunately, the sqlite date, datetime, strftime functions will not recognise the first format.
> I was going to manually change all dates to one format using sqlite to directly update the backend.  However, I can see that other tables interact / depend on the format.  In particular the slots table: timespec_val.
> How can I make GnuCash store all dates in only 1 format?  Is there an option via the gui somewhere?  I don't believe I told GnuCash to change the format it stores dates in, so why did it change its format?
> Thanks for any help / suggestions.

GnuCash3 does store only one string format, the second one--but GnuCash 2.4 and 2.6 stored only one string format, the first one. I changed it while rewriting the SQL backend so that the same format applied to all databases, getting rid of the custom formatter for SQLite and removing the need to know which DB engine the output was for. Since GnuCash generally reads the whole database into memory but writes back only new or changed records any SQLite3 database created in 2.6 is likely to have mixed formats.

The simplest way to get all date-times in the new format is to just save your book to XML then open that and save it back to SQLite3.

Regards,
John Ralls