how to insert current datetime in the sql command

11,397

You should use a parametized query instead of concatenating strings, what you are doing is asking for an SQL Injection. Also, you should dispose commands and connections after use them in order to release memory.

using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString))
{
    using (var command = new SqlCommand("insert into sms (col1) values(@col1"))
    {
        command.Parameters.AddWithValue("@col1", DateTime.Now);
        con.Open();
        command.ExecuteNonQuery();
    }
}
Share:
11,397
dotnetcoder
Author by

dotnetcoder

Updated on June 04, 2022

Comments

  • dotnetcoder
    dotnetcoder almost 2 years

    how to insert the current time using ado.net sql command. getting error saying The "conversion of a varchar data type to a datetime data type resulted in an out-of-range value."

    code

     DateTime NowTime = DateTime.Now;
                    string usecase = "manhole";
                    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString);
                    con.Open();
                    SqlCommand cmdInsert = new SqlCommand("insert into sms values('" + usecase + "','" + smsbody + "','" + NowTime + "')", con);
                    try
                    {
                        cmdInsert.ExecuteNonQuery();
                    }
    
    
        columns
        updtd_date is datetime
    
        query
             INSERT INTO sms (usecase, sms, updtd_date)
    VALUES ('manhole','level is low at : 22/01/2018 15:56:20','22/01/2018 16:18:28'); 
    
    • Crowcoder
      Crowcoder about 6 years
      If you use parameters you don't need to worry about date formatting because you are inserting an actual DateTime, not a string that has to be parsed (not to mention the prevention of sql injections).
  • BugFinder
    BugFinder about 6 years
    <cough> SQL Injection.. parameters
  • Nisarg Shah
    Nisarg Shah about 6 years
    This is a workaround, not a solution. Also, if the SQL server is in a different timezone as compared to the application server, the timestamp would be different here.
  • Jayasurya Satheesh
    Jayasurya Satheesh about 6 years
    @NisargShah, Yes, I agree With that. Otherwise, he can pass the System Date Time in the 2nd Solutions
  • BugFinder
    BugFinder about 6 years
    Much better. Safety first and all that
  • Zohar Peled
    Zohar Peled about 6 years
    That's the only answer so far that actually addresses the real problems with the code in the question. +1.