I have the following SqlCommand that inserts a row of data to a SQL Server database table. In particular, the Value column is of datatype DateTime.

When the command is ran, the date format is:

Sep 11 2003 12:00AM

Data when viewing in SQL Server database

However, it needs to be 2003-10-11

When I view it in an application, the date column is blank, but when I change the data directly in the database to match yyyy-mm-dd, the column value is visible in the application.

How do I change the format of the date?

using (SqlConnection connection = new SqlConnection(newConnStr))
{
    connection.Open();

    string dateOfInterviewQS =
           "INSERT INTO PropertyValues(PropertyId, UserId, Value, FriendlyName, LastUpdated, CheckDate) "   
           " VALUES(@PropertyId, "   "@UserId, "   "@Value, "  
                    "@FriendlyName, "   "@LastUpdated, "   "@CheckDate)";

    using (var cmd = new SqlCommand(dateOfInterviewQS, connection))
    {
        SqlParameter date = cmd.Parameters.Add("@Value", SqlDbType.DateTime);
        date.Value = DateOfInterview;

        cmd.Parameters.AddWithValue("@PropertyId", 2);
        cmd.Parameters.AddWithValue("@UserId", newUserId);
        cmd.Parameters.AddWithValue("@FriendlyName", DBNull.Value);
        cmd.Parameters.AddWithValue("@LastUpdated", DateTime.Now);
        cmd.Parameters.AddWithValue("@CheckDate", DateTime.Now);

        cmd.ExecuteNonQuery();
    }

    connection.Close();
}

CodePudding user response：

There are a few things fundamentally wrong here...

1. Can we stop using AddWithValue() already?
2. Storing a DateTime value as a string is a bad idea. Store data as data. Then format it for display when you need to display it. A DateTime is natively understood by the system, easily sortable, can be used to perform date calculations, etc. A string is just text. (The format you have is coincidentally sortable, but deliberate management of data is always better than something that coincidentally works.)

Having said that...

You can use .ToString() to format your DateTime. For example:

cmd.Parameters.AddWithValue("@LastUpdated", DateTime.Now.ToString("yyyy-MM-dd"));

Or, if you want single-digit months/days:

cmd.Parameters.AddWithValue("@LastUpdated", DateTime.Now.ToString("yyyy-M-d"));

Basically, since you're storing a string, you need to send the database a string. Not a DateTime.

(But, again, you should be storing a DateTime and formatting it as a string when using it downstream.)