What is the ADO.net parameterization for "Now"?

Question

I have an application that is storing values in a Jet Blue database. I build, and issue, a query such as:

INSERT INTO Hisotry (DeathDate, Frob, Grobber)
VALUES (Now(), "Unilatteral phase detractor's", "Sinusoidal depleneration")

Now I'm migrating to a different database, and I figured it might be time to use that pain in the ass fancy parameterization:

void Trunnions(DbConnection connection, String frob, String grobber)
{
   String sql = 
         "INSERT INTO Hisotry (DeathDate, Frob, Grobber)" + "\r\n"+
         "VALUES (@deathDate, @frob, @grobber)";

   using (DbCommand cmd = connection.CreateCommand())
   {
      cmd.CommandText = sql;
      DbParameter param;

      param = cmd.CreateParameter();
      param.Name = "@deathDate";
      param.DbType = DbType.DateTime;
      param.Value = "Now()";
      cmd.Parameters.Add(param);

      param = cmd.CreateParameter();
      param.Name = "@frob";
      param.DbType = DbType.StringFixedLength;
      param.Value = frob;
      cmd.Parameters.Add(param);

      param = cmd.CreateParameter();
      param.Name = "@grobber";
      param.DbType = DbType.String;
      param.Value = grobber;
      cmd.Parameters.Add(param);

      cmd.ExecuteNonQuery();
   }
}

Of course, trying to use Now() as a value for a DbType.DateTime parameter is not valid.

I could have used:

param = cmd.CreateParameter();
param.Name = "@deathDate";
param.DbType = DbType.DateTime;
param.Value = DateTime.Now;
cmd.Parameters.Add(param);

except that's not correct - it uses the local machine's clock rather than the server's.

The only reason I ask is because I don't know what the new database engine's syntax for Now() is. I know some can use:

• getdate()
• getutcdate()
• SYSDATETIME()
• SYSUTCDATETIME()
• SYSDATETIMEOFFSET()
• CURRENT_TIMESTAMP()
• NOW()
• LOCALTIMESTAMP()

And I remember it took me hours in 2008 to figure out what Jet Blue used. i'd rather have the ADO.net solution.

Answer 1

Assuming you are using SQL Server:

1. Change the "Now()" to DateTime.Now() without the quotes

2. Change the insert statement to be:

   String sql = 
       "INSERT INTO Hisotry (DeathDate, Frob, Grobber)" + "\r\n"+
       "VALUES (GetDate(), @frob, @grobber)";