FireDAC Date Handling – FireDAC Date Mystery Part II
As it turns out, the mystery was not fully solved.
After adding map rules (see old post) to FireDAC for how to handle dtTime and dtDate, I removed the old ADO special case handling and just did
Result := FieldByName(FieldName).AsDateTime;
Which worked well – or – so I thought. I had done all my testing on machines that had the SQL Server Native Client installed, and it showed no problems.
But – when I ran on a machine without SQLNCLI installed, the date format issue reared it’s ugly head again.
So – this is the “final” workaround which so far appears to handle every date/time format that I have tossed at it.
function TPSDRecordset.GetAsDateTime(const FieldName: string): TDateTime;
var
F: TField;
s: String;
begin
F := FieldByName(FieldName);
try
case F.DataType of
ftDateTime: Result := F.AsDateTime;
ftTime: Result := StrToTime(F.AsString);
ftDate: Result := StrToDate(F.AsString);
else begin
OutputDebugString(‘GetAsDateTime(‘+FieldName+’).DataType=’ + IntToStr(Ord(F.DataType)));
Result := StrToDateTime(F.AsString);
end;
end;
except
on E:Exception
do begin
s := FieldByName(FieldName).AsString;
OutputDebugString(Self.ClassName + ‘.GetAsDateTime(‘ + FieldName + ‘) = ‘ + s + ‘: ‘ + E.ClassName +’ – ‘ + E.Message);
raise;
end;
end;
#FireDAC #SQLServer #NativeClient
Originally shared by Lars Fosdal
A FireDAC Date Mystery
I have an app that connects to a MSSQL db through FireDAC and retrives a selection of log data, including the date and time of the logging in a datetime field.
I leave the app open for a longer period of time (about an hour, I think), then I refresh the view.
BAM! The dates show up as 1899. For some reason, FireDAC has decided to forget how to get the datetime in the right format.
I suspect that is is related to a connection object that has lost it’s connection, but why can it reconnect and get data, and yet get the date format wrong?
Which type is in this?
OutputDebugString(‘GetAsDateTime(‘+FieldName+’).DataType=’ + IntToStr(Ord(F.DataType)));
None that I have seen so far. It’s a safe measure to help me catch any missing conversions.
I use an ApplicationEvents component to keep the date and time format how like them:
void __fastcall TDMSystem::ApplicationEvents1SettingChange(TObject *Sender, int Flag,
const UnicodeString Section, int &Result)
{
if (Section == “intl” && Flag == 0) {
FormatSettings.DateSeparator = ‘-‘;
FormatSettings.LongDateFormat = “yyyy-MM-dd”;
FormatSettings.ShortDateFormat = “yyyy-MM-dd”;
FormatSettings.LongTimeFormat = “hh:mm:ss am/pm”;
FormatSettings.ShortTimeFormat = “hh:mm:ss”;
FormatSettings.TimeAMString = “am”;
FormatSettings.TimePMString = “pm”;
}
}
Wrong language but maybe it’ll help. I was getting date reformatting that I didn’t want and this dis the trick.
It’s complicated, since the formatting has to follow the locale of the user. As an example, we don’t use am/pm here, but a 24h clock, and we do dates like dd.mm.yyyy. The databases also may have differing date formats.
I try not to tamper with the default format settings, but create a custom one to pass along if I need a fixed format, like for the ERP data.
Understood. FireDAC uses the global FormatSettings and what I found was that some system event was causing the VCL to reset FormatSettings to some default values. The database I use returns “yyyy-mm-dd” and then would mysteriously change to “mm/dd/yyyy”.
Maybe it’s a system wake up event – something like that?