title: How to update a DateTime on SQL Server preserving milliseconds using Delphi 7 ADOQuery.ExecSQL?
body: <p>How do you update a DateTime on SQL Server 2012 preserving milliseconds using Delphi 7 <code>ADOQuery.ExecSQL</code>?</p>
  
  <p>SQL server seems to drop the seconds precision so I do not have milliseconds or microseconds on a SQL Server 2012 <code>DateTime2[7]</code> 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 <code>DateTime</code>.</p>
  
  <p>The connection string I am using is: 
  <code>Provider=SQLNCLI11.1;Integrated Security=SSPI;User ID="";Initial File Name="";Server SPN="";Data Source=myPC\SQLSERVER2012EXP;Initial Catalog=MyDatabase</code></p>
  
  <p>Here is my code:</p>
  
  <pre><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 &lt;&gt; nil);
    assert( Sql &lt;&gt; '');
    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 &lt;&gt; '');
  
    // 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;
  </code></pre>
  
  <p><strong>UPDATED</strong></p>
  
  <p>Modified code for MS Access and SQL Server:</p>
  
  <pre><code>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;
  </code></pre>