FireDAC Date Handling – FireDAC Date Mystery Part II

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?

#FireDAC  

5 thoughts on “FireDAC Date Handling – FireDAC Date Mystery Part II


  1. 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.

    Like


  2. 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.

    Like


  3. 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?

    Like

Leave a Reply to jeff weir Cancel reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.