Value from a "DATE data-type" field

fabio_rbs · Post by **fabio_rbs** » Thu 17 Mar 2011 17:25

Hi there,

I'm having wrong results when getting value from DATE fields on Firebird.
Always the year comes incorrect. Please, see the code below:

Code: Select all

procedure TFrmPrincipal.Button1Click(Sender: TObject);
var
  DBXTrn: TDBXTransaction;
  DBXCmd: TDBXCommand;
  DBXRdr: TDBXReader;
  dDta: TDateTime;
begin
  dDta := 0;

  if DM.DBXCon.Connected then
    begin
      DBXTrn := DM.DBXCon.DBXConnection.BeginTransaction(TDBXIsolations.ReadCommitted);
      try
        DBXCmd := DM.DBXCon.DBXConnection.CreateCommand;
        try
          DBXCmd.Text := 'SELECT CAST(''NOW'' AS DATE) AS MYDATE' +
                         '  FROM RDB$DATABASE';
          DBXCmd.Prepare;
          DBXRdr := DBXCmd.ExecuteQuery;
          if DBXRdr.Next then
            dDta := DBXRdr.Value['MYDATE'].GetDate;

          DBXCmd.Close;
          DBXRdr.Free;
        finally
          DBXCmd.Free;
        end;

        DM.DBXCon.DBXConnection.CommitFreeAndNil(DBXTrn);
      except
        DM.DBXCon.DBXConnection.RollBackFreeAndNil(DBXTrn);
        raise;
      end;
    end;

  ShowMessage(FormatDateTime('dd/mm/yyyy', dDta));
end;

The result is "16/03/3910", the correct is "16/03/2011".

The RDB$DATABASE was used for sample, but this happens with any date field.

Does anyone knows about this ?

To try the sample don't forget to add DBXCommon to your uses clause.
Firebird 2.1.4 - Delphi 2010 - DBXIda 2.70.28

(By the way, the new resource "lock timeout for wait transactions" is working perfectly).

Thanks,

Fábio R. Bot da Silva.

AndreyZ · Post by **AndreyZ** » Fri 18 Mar 2011 09:32

Hello,

The point is that in this case DATE values are returned in the TimeStamp format. To solve the problem you should use the following code:

Code: Select all

procedure TFrmPrincipal.Button1Click(Sender: TObject);
var
  DBXTrn: TDBXTransaction;
  DBXCmd: TDBXCommand;
  DBXRdr: TDBXReader;
  dDta: TDBXDate;
  tst: TTimeStamp;
begin
  dDta := 0;

  if DM.DBXCon.Connected then
    begin
      DBXTrn := DM.DBXCon.DBXConnection.BeginTransaction(TDBXIsolations.ReadCommitted);
      try
        DBXCmd := DM.DBXCon.DBXConnection.CreateCommand;
        try
          DBXCmd.Text := 'SELECT CAST(''NOW'' AS DATE) AS MYDATE' +
                         '  FROM RDB$DATABASE';
          DBXCmd.Prepare;
          DBXRdr := DBXCmd.ExecuteQuery;
          if DBXRdr.Next then
            dDta := DBXRdr.Value['MYDATE'].GetDate;

          DBXCmd.Close;
          DBXRdr.Free;
        finally
          DBXCmd.Free;
        end;

        DM.DBXCon.DBXConnection.CommitFreeAndNil(DBXTrn);
      except
        DM.DBXCon.DBXConnection.RollBackFreeAndNil(DBXTrn);
        raise;
      end;
    end;

  tst.Time := 0;
  tst.Date := dDta;
  ShowMessage(FormatDateTime('dd/mm/yyyy', TimeStampToDateTime(tst)));
end;

fabio_rbs · Post by **fabio_rbs** » Mon 21 Mar 2011 17:57

OK.
I can't understand why it's coming as Timestamp.
But your code is working fine.

Thanks.

AndreyZ · Post by **AndreyZ** » Tue 22 Mar 2011 07:04

Feel free to contact us if you have any further questions about dbExpress driver for InterBase.