How to update a DateTime on SQL Server preserving milliseconds using Delphi 7 ADOQuery.ExecSQL?

Question

How do you update a DateTime on SQL Server 2012 preserving milliseconds using Delphi 7 ADOQuery.ExecSQL?

SQL server seems to drop the seconds precision so I do not have milliseconds or microseconds on a SQL Server 2012 DateTime2[7] field. MS Access does not drop the milliseconds with this ADO Query. The following ADOQuery code seems to work with all DataTypes that I care about except SQL Server DateTime.

The connection string I am using is: Provider=SQLNCLI11.1;Integrated Security=SSPI;User ID="";Initial File Name="";Server SPN="";Data Source=myPC\SQLSERVER2012EXP;Initial Catalog=MyDatabase

Here is my code:

function ExecuteNonQry(Conn:TADOConnection;Sql:string;Params:Variant): Integer;
{ Execute a query that does not return a recordset.  Returns number of rows 
  affected. Params can be any unique name, they are all dealt with in order
  of appearance in the parameter array and in the sql string.
  E.g. SQL 
    'INSERT INTO customers (id,name,country) VALUES (:id,:name,:country)';
  E.g. calling code:
    sql := 
     'INSERT INTO Sessions (SessionType_cod , HasErrors, Start_Timestamp) ' + 
     ' VALUES (:1, :2, :3)';
    params := VarArrayOf([ 1, true, Now ]);
    ExecuteNonQry( GvConnection1, sql, params );

    Note: Do not use ADO with Paradox - will be slow and possibly error prone.

    Tested the following DataTypes with "Insert Into" and "Update" queries
    **********************************************************************
    Delphi          MS Access    SQL Server      Paradox
    -----------------------------------------------------
    ftInteger       Long         int
    ftString        Text(255)    nvarchar(255)
    ftString        Memo         nvarchar(max)
    ftBoolean       Boolean      bit
    ftDateTime      Date         datetime2(7)
    ftDouble        Double       float
}
var
  qry : TADOQuery;
begin
  assert( Conn <> nil);
  assert( Sql <> '');
  qry := TADOQuery.Create(nil);
  qry.DisableControls;
  qry.SQL.Text := Sql;
  AddParametersToQuery(qry, Params);
  qry.Connection := Conn;
  result := qry.ExecSQL;
end;

procedure AddParametersToQuery(var Qry: TADOQuery; Params: Variant);
{ Version 1b.  (Uses Delphi function to replace "DIRegEx" dependencies)
  Add parameters (type and value) to ADO query. ADOQuery must have SQL 
  text set and Params is a variant array. 
  Limitations: SQL Server drops DateTime second precision digits for 
  milliseconds or microseconds.
  E.g. Sql: 
    'INSERT INTO Sessions (SessionType_cod , HasErrors, Start_Timestamp) 
     VALUES (:1, :2, :3)';
  or  Sql: 
    'INSERT INTO Sessions (SessionType_cod , HasErrors, Start_Timestamp) 
     VALUES (:mykey, :HasErrors, :myDateTime)';
}
const
  regPattern = ':';
var
  str: String;
  val: Variant;
  i: Integer;
  sl: TStrings;
begin
  assert( Qry.SQL.Text <> '');

  // in some cases this is necessary.
  Qry.Parameters.ParseSQL(Qry.SQL.Text, true);        

  sl := TStringList.Create;
  try
    // find all param wordssql text such as '1, 2, 3'
    sl := ExtractWordsToStrings(':', Qry.SQL.Text);   

    // loop through any matches found
    for i := 0 to sl.Count -1 do            
    begin
      str := sl[i];
      val := GetVarParam(i, Params);
      // in some cases this is necessary.
      Qry.Parameters.ParamByName(str).DataType := 
          VarTypeToDataType(Ord(VarType(val)) );
      Qry.Parameters.ParamByName(str).Value := val;
    end;
  finally
    sl.Free;
  end;
end;

UPDATED

Modified code for MS Access and SQL Server:

if (VarType(val) = varDate) And IsSqlServerProvider(Conn.Provider) then begin
  // needed for SQL Server
  Qry.Parameters.ParamByName(str).Value := 
     FormatDateTime('yyyymmdd hh:nn:ss.zzz', val)   
end
else
begin
  // in some cases this is necessary.
  Qry.Parameters.ParamByName(str).DataType := 
     VarTypeToDataType(Ord(VarType(val))); 
  Qry.Parameters.ParamByName(str).Value := val;
end;

Unconventionality

To preserve the milliseconds you could simply declare the column in the database using TDateTime's native type: double. It would mean getting TFloatField instead, but IIRC they have an AsDateTime member. – Unsuccess

Answer 1

DateTime2(7) via Ado is seen as TWideStringField.
Datetime will be seen as TDateTimeField.
Internal conversion if assigning a TDatetime will ignore milliseconds like DateTimeToStr(dt).
You can handle this by using own conversion to WideString.

Function MyDateTimeString(d:TDateTime):String;
begin
  Result := FormatDateTime('yyyymmdd hh:nn:ss.zzz',d);
end;

procedure TForm1.Button1Click(Sender: TObject);
var
dt:TdateTime;
begin
  dt := now;
  Caption := FormatDateTime('dd.mm.yyyy hh:nn:ss.zzz',dt);
  Adoquery1.Paramcheck := true;
  Adoquery1.SQL.Text := 'Insert into Tab (a,b,DT) Values (:a,:b,:DT)';
  Adoquery1.Parameters.ParseSQL(Adoquery1.SQL.Text,true);
  Adoquery1.Parameters.ParamByName('a').Value := 1;
  Adoquery1.Parameters.ParamByName('b').Value := 2;
  Adoquery1.Parameters.ParamByName('DT').Value := MyDateTimeString(dt);
  Adoquery1.ExecSQL;
end;

using then following will lead to rounding:

  Adoquery1.Parameters.ParamByName('DT').DataType := ftDateTime;
  Adoquery1.Parameters.ParamByName('DT').Value := dt;

Microsporangium

This does work nicely for SQL server thanks! I was hoping that the ParamByName would also work for MS Access. Any ideas to make it work for both? Failing that I guess I will have to figure out what type of connection by parsing the connection string and setting the parameter accordingly. I haven't found a nice ADO enumerated type to find what type of connection you have so the connection string it is... – Unconventionality

var i:Integer; begin for I := 0 to AdoConnection1.Properties.Count - 1 do begin Listbox1.Items.Add(AdoConnection1.Properties[i].Name+'='+VarToStr(AdoConnection1.Properties[i].Value)) end; Showmessage(AdoConnection1.Properties.Item['Provider Friendly Name'].Value ); end; //or better use 'Provider Name' – Microsporangium

Thanks. The "Provider Name" string in the TADOConnection.Provider property works well. – Unconventionality