Insert a smalldatetime into SQL Server

I am trying to insert date to a smalldatetime column in SQL Server

I try something like this:

DateTime  transfer_date;
transfer_date = DateTime.Now;

SQL = "insert into MyTbl (DateT) values (transfer_date)";

SqlCommand Cmd_SQL = new SqlCommand(SQL, Conn_SQL);
Cmd_SQL.CommandText = SQL;
Cmd_SQL.ExecuteNonQuery();

but I got this error:

The conversion of a varchar data type to a smalldatetime data type resulted in an out-of-range value. The statement has been terminated.

Jon Skeet
people
quotationmark

You're currently not doing anything with your transfer_date variable at all. Your SQL statement contains the text transfer_date, but it doesn't automatically fetch the value from the database. You want something like:

// @transfer_date is now a *parameter*.
string sql = "insert into MyTbl (DateT) values (@transfer_date)";

// Avoid using a shared connection - it'll cause problems. Let the connection
// pooling do its job. But use using statements to ensure that both the connection
// and the statement are disposed.
using (var connection = new SqlConnection(...))
{
    connection.Open();
    using (var command = new SqlCommand(sql, connection))
    {
        // No need to set the CommandText value now - it's already set up above.
        // But we need to set the value of the parameter.
        command.Parameters.Add("@transfer_date", SqlDbType.SmallDateTime).Value
             = DateTime.Now;
        command.ExecuteNonQuery();
    }
}

people

See more on this question at Stackoverflow