SOLVED StrToDateTime result (ver. from 0.9 to 1.1) and Julian day in sqlite DB

[agb]

Hi!

Code:
===
dtf.DateSeparator := '-';
      dtf.TimeSeparator := ':';
      dtf.ShortDateFormat := 'yyyy-MM-dd';
      dtf.ShortTimeFormat := 'hh:mm:ss';
      dt := StrToDateTime(s, dtf);
...
DM.quRecords.InsertRecord([id,
                                 dt,    // whendate
                                 data.Strings[1]]);   // tel 
...
===
I got Database records with different values. One year before:
id  whendate
--  --------
xx 40994.59472

And after change Lazarus version:
id  whendate
--  --------
xx 2456013,119

All records are in one sqlite datebase now. And I do not know what to do to fix this.
SQLite now normally converts 2456288,423 to '2012-12-26', so I need to convert old records to this format.

[wjackson153]

Not sure If I understand what your asking entirely :

but perhaps this would help

dtf := FormatDateTime('yyyyy-mm-dd',DATE) ;

[BigChimp]

wjackson's advice sounds good. AFAIR, behaviour of default date/time delimiters has changed in previous FPC versions. No idea if this affects you...

[agb]

Two functions returns two different values:
StrToDateTime(s, dtf) in code:
===
dt := StrToDateTime(s, dtf);
DM.quRecords.InsertRecord([id, dt]);
===
inserts Julianday numbers approx 2456013,119 to database


The similar function StrToDate in code:
===
s := s + 'round(whendate-0.5) = ' + IntToStr(trunc(StrToDate(edDate.Text)));
===
add number approx 40995 (numeric date in Excel) to string. (edDate: TDateEdit)

----------
solution is:
s := s + 'round(whendate - 1) = ' + IntToStr(trunc(DateTimeToJulianDate(edDate.Date)));

----------
And in lazarus 0.9 StrToDateTime puts to database values approx 40995 (like date in Excel).

[Zoran]

AFAIR, behaviour of default date/time delimiters has changed in previous FPC versions.

Uh... Is it documented somewhere?

In the pages to which this page links I cannot find it.

[marcov]

That page is about new features, not general changes

Every FPC release has a "User changes" page that tries to document the changes needed to user code when changing FPC version. Specially for major FPC version changes (2.4.x -> 2.6.x) it can be worthwhile to investigate.

The problem you mention is in the user changes for 2.6.0:

http://wiki.freepascal.org/User_Changes_2.6.0#SQLDB.2FTSQLite3Connection_now_stores_date.5Btime.5D_values_as_Julian_day_numbers

The entry contains a SQL query to convert such columns

[agb]

marcov, thank you!