Why it always saves the time in the date to the database as zero C#

Question

It always saves the date column in the database like this: 2016-10-16 00:00:00.000. And I want it saved like this: 2016-10-16 14:13:56.000. I cannot save the time information to the database. The data type of date column in the database is also defined as datetime.

RecordDate(datetime,null)

When I debug the code, date shows "2016.10.16 14:13:56".

using (var conn = new SqlConnection())
            {
                conn.ConnectionString = connectionDBString;
                conn.Open();
                using (var command = conn.CreateCommand())
                {
                    command.CommandText = $"INSERT INTO RECORDS VALUES('{records.Id}','{records.Type}','{records.Title}', @P0)";
                    if (records.Date != null)
                    {
                        command.Parameters.AddWithValue("P0", records.Date.Value);
                    }
                    command.ExecuteNonQuery();
                }
                conn.Close();
            }

Type of records.Date is

DateTime?

Please share a few more relevant details: the property declaration for `record.Date`, the the code you're using to save it to the database, and the definition for the column it's being saved to. — StriplingWarrior, May 31 '21 at 03:16
I checked again but the data type is datetime. I also edited the question. — andrei_ww, May 31 '21 at 03:33
@andrei_ww Your code has DateTime, but what does the database have?? — Loren Pechtel, May 31 '21 at 03:56
@LorenPechtel The name of the column I added the date in the table is RecordDate. Data type is a DateTime. — andrei_ww, May 31 '21 at 03:58
BTW, two other issues here: First, you're inlining a text field. That means you're trusting it's sanitized and sanitizing **and then restoring** text can be a royal pain. It's much easier and safer to always use parameters for text. Second, look at happens when records.Date is null. — Loren Pechtel, May 31 '21 at 04:02
`record.Date.Value` Please show us that `Date` property. The actual code of it. — mjwills, May 31 '21 at 04:08
Please share the `CREATE TABLE` script for `RECORDS`. Also, which database platform is this for? — mjwills, May 31 '21 at 04:09
`When I debug the code, date shows "2016.10.16 14:13:56".` Where _specifically_ are you seeing that value? — mjwills, May 31 '21 at 04:10
Please read https://stackoverflow.com/questions/14376473/what-are-good-ways-to-prevent-sql-injection as a matter of urgency. — mjwills, May 31 '21 at 04:10
How specifically are you verifying that the time is _not_ stored in the database? — mjwills, May 31 '21 at 04:12
Why do you use records.Xxx everywhere else but add record.Date to the parameter? `records` is one variable and `record` is another? — Caius Jard, May 31 '21 at 04:29
@andrei_ww You use string interpolation with SQL parameters, that's not logical. [What are good ways to prevent SQL injection?](https://stackoverflow.com/questions/14376473/what-are-good-ways-to-prevent-sql-injection) | [SqlCommand Parameters Add vs. AddWithValue](https://stackoverflow.com/questions/21110001/sqlcommand-parameters-add-vs-addwithvalue) — , May 31 '21 at 05:36

Caius Jard · Answer 1 · 2021-05-31T05:14:56.263

Always parameterize all your values:

using (var command = conn.CreateCommand())
{
  command.CommandText = $"INSERT INTO RECORDS VALUES(@id,@ty,@ti, @d)";
               
  command.Parameters.Add("@id", SqlDbType.VarChar, 50).Value = records.Id);

  command.Parameters.Add("@ty", SqlDbType.VarChar, 50).Value = records.Type);

  command.Parameters.Add("@ti", SqlDbType.VarChar, 50).Value = records.Title);

  //remove this line when you’re satisfied that the inserting of times does work
  records.Date = DateTime.Now;

  command.Parameters.Add(new SqlParameter("@d", SqlDbType.DateTime2) { Scale = 3, Value = (object)records.Date ?? DBNull.Value });

}

I have ..

guessed you are using sql server, but if you aren’t you’ll need to use different names for the type enum than SqlDbType, which is for sql server
specified all the parameters, which you should always do because not doing so is a bad idea
switched away from using AddWithValue, because it’s a bad idea for sql server but it’s not a problem for some other DB; investigate whether it’s a good idea for you
guessed at the column types from what you said and coded. I do not believe id to actually be a string, but you put it in ' in the statement - don’t just blindly write things in ' if they aren’t strings in the db. Change the types and sizes specified to match what your columns actually are
added a test line that sets your records.Date to a date time with a time. Don’t run the code bang on midnight :) - if your property isn’t writable, make it so or swap it out out the Value and put DateTime.Now in. I assumed record.Date was a typo

If your db column is a DateTime then this code will certainly insert a date and time

Omitting the column name list from INSERT will work while your table has 4 columns but if it changes and you add another the query will fail. This is either a good thing or a bad thing. Good if you want to stop your system working while you fix the problem, bad if your new column is optional and everything could have carried on working if you’d just specified the column names. Generally it makes more sense to specify names but if you’re wanting to code more efficiently consider moving away from this style of coding, where you’re writing your own SQL, and use Entity Framework

Why it always saves the time in the date to the database as zero C#

1 Answers1