ASP.NET MVC : cannot retrieve hours, minutes, seconds from SQL database, they always show 12:00

Question

Note: I am also looking for a way to AddWithParameter to avoid the SQL injection risk, but I am not sure how to do that with DotNetNuke so that is a second question to this matter.

I am storing date as C# DateTime into SQL database datetime column. When retrieving that date I cannot obtain the hh:mm:ss information, it always displays 12:00, but the Day, Month, Year are retrieved and displayed correctly.

This is how I insert the date in SQL:

string sqlDateFormat = "yyyy-MM-dd HH:mm:ss.fff";               
string sqlInsertString = String.Format("INSERT INTO TABLE_NAME (Date_Column_Name) VALUES ({0})", thisDate?.ToString(sqlDateFormat)};

using (IDataContext ctx = DataContext.Instance())
{
    try
    {                  
        ctx.Execute(System.Data.CommandType.Text, 
        sqlInsertString);      
        success = true;                                                     
    }
    catch (Exception ex)
    {
        System.Diagnostics.Debug.WriteLine(ex.Message);
    }
}

This is how I retrieve the table data:

using (IDataContext ctx = DataContext.Instance())
{
    string sqlString = @"SELECT Date_Column_Name FROM Table_Name  WHERE ID = " + paramId;

    try
    {
        myTableDates = ctx.ExecuteQuery<TableModel> (System.Data.CommandType.Text, sqlString);
    }
    catch (Exception ex)
    {
        System.Diagnostics.Debug.WriteLine(ex.Message);
    }
}

This is how I try to display in view:

<tbody>
    @foreach (TableModel tm in Model.AllTablesDates)
    {
        var dateVar = tm.Date == null ? "N/A" : 
        tm.Date.Value.ToString("dd-MMM-yyyy h:mm tt");

        <tr>
            <td data-title="Day">@dateVar</td>                        
        </tr>
    }
</tbody>

Answer 1

Based on this post change your statement to the following:

ctx.Execute("INSERT INTO TABLE_NAME (Date_Column_Name)VALUES (@0)", thisDate);