Insert TDateTime field to SQL 2008 DB using Native Client 10

Question

We have a legacy application that is written in Delphi 2007 and is still using the BDE (yes it needs to be switched to ADO but with over 500K lines, that's a BIG job). It connects to an SQL Server 2008 DB using an SQL SERVER ODBC connection. I am playing around with switching to the SQL Server Native Client 10.0 instead and have come across an interesting issue. When trying to insert a record in to a table that contains datetime fields, we are getting the following error:

Project DateTimeParamTest.exe raised exception class EDBEngineError with message 'General SQL error.
[Microsoft][SQL Server Native Client 10.0]Datetime field overflow. Fractional second precision exceeds the scale specified in
the parameter binding.'.

In doing some research, I have seen comments to play with the NumericScale, Precision, and Size parameters of the TParameter object. A TADOQuery will automatically set the parameters to 3, 23, and 16 respectively and has no problem with the insert. If I set the parameters to the same on the TQuery object, I get the same error as above.

Does anyone have any experience with this and know of an easy work-around? I created the following sample code for anyone wanting to try. You will just need to change the connection and SQL code.

DateTimeParamTest_Main.dfm:

object Form10: TForm10
  Left = 0
  Top = 0
  Caption = 'Form10'
  ClientHeight = 111
  ClientWidth = 181
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'Tahoma'
  Font.Style = []
  OldCreateOrder = False
  PixelsPerInch = 96
  TextHeight = 13
  object Button2: TButton
    Left = 20
    Top = 16
    Width = 75
    Height = 25
    Caption = 'BDE'
    TabOrder = 0
    OnClick = Button2Click
  end
  object dbPMbde: TDatabase
    AliasName = 'PMTest'
    DatabaseName = 'DB'
    LoginPrompt = False
    SessionName = 'Default'
    Left = 20
    Top = 52
  end
  object qryBDE: TQuery
    DatabaseName = 'DB'
    SQL.Strings = (
      'INSERT INTO TRAN_DETAIL (ID, STARTDATE, ENDDATE)'
      'VALUES (:ID, :STARTDATE, :ENDDATE);')
    Left = 88
    Top = 52
    ParamData = <
      item
        DataType = ftInteger
        Name = 'ID'
        ParamType = ptInput
      end
      item
        DataType = ftDateTime
        Precision = 23
        NumericScale = 3
        Name = 'STARTDATE'
        ParamType = ptInput
        Size = 16
      end
      item
        DataType = ftDateTime
        Precision = 23
        NumericScale = 3
        Name = 'ENDDATE'
        ParamType = ptInput
        Size = 16
      end>
  end
end

DateTimeParamTest_Main.pas:

unit DateTimeParamTest_Main;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, DB, DBTables;

type
  TForm10 = class(TForm)
    Button2: TButton;
    dbPMbde: TDatabase;
    qryBDE: TQuery;
    procedure Button2Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form10: TForm10;

implementation

{$R *.dfm}

procedure TForm10.Button2Click(Sender: TObject);
begin
  dbPMbde.Open;
  with qryBDE do
  begin
    parambyname('ID').Value := 99999999;
    parambyname('StartDate').Value := now;
    parambyname('EndDate').Value := now;
    execsql;
  end;
  dbPMbde.Close;
end;

end.    

Answer 1

This appears to be an issue with the milliseconds causing a data type overflow (based on the comments above). If so, there is a quick fix that comes to mind.

Replace the assignment to the date columns like this:

qryBDE.ParamByName('STARTDATE').AsDateTime := FixBDEDateTime(Now);

where FixBDEDateTime is simply

function FixBDEDateTime(const Value: TDateTime): TDateTime;
var
  Year, Mon, Day, Hr, Min, Sec, MS: Word;
begin
  DecodeDate(Value, Year, Mon, Day);
  DecodeTime(Value, Hr, Min, Sec, MS);
  Result := EncodeDate(Year, Mon, Day) + 
            EncodeTime(Hr, Min, Sec, 0);
end;

Edit: As @TLama points out in the comments (and I didn't mention because I missed the Delphi 2007 in the opening sentence and didn't see a version in the tags), you can also just use:

uses
  DateUtils;
...

qryBDE.ParamByName('STARTDATE').AsDateTime := RecodeMillisecond(Now, 0);