How to get SQL Server datetime value as it is in 24 hours in C#

I am developing an app in VS2010 c# to fetch a single row data from SQLServer and insert it to MySQL.

I have a table with column name Date_Time containing date and time in 24 hrs. format as shown in below image.

date and time in 24 hrs. format

Fetching code is as below.

SqlCommand cmd = new SqlCommand("SELECT TOP (1) s_name, s_city, s_address, s_added_date, s_added_by FROM tblAQI ORDER BY s_added_date DESC", SSCon);
    SqlDataReader dr = cmd.ExecuteReader();
    while (dr.Read())
    {
        s_name = (dr["s_name"].ToString());
        s_city = (dr["s_city"].ToString());
        s_address = (dr["s_address"].ToString());
        s_added_date = (dr["s_added_date"].ToString());
        s_added_by = (dr["s_added_by"].ToString());
    }

when I print the value of s_added_date it gives me value of s_added_date

My question is why it showing like this and how can I get this time in 24 hrs. format.

Please help to resolve it.

Thanks in advance.

Jon Skeet
people
quotationmark

I have a table with column name Date_Time containing date and time in 24 hrs. format

No, you have a table with a column type of DateTime. The values don't inherently have any format - they just happen to be displayed one way in your SQL results viewer, which isn't the same way as .NET formats them by default.

It's very important to understand that the data here is just the date and time - not the format.

To format it in a particular way, cast it to DateTime and then use a ToString overload which allows you to specify the format:

DateTime addedDate = (DateTime) sr["s_added_date"];
string addedDateText = addedDate.ToString("dd-MMM-yyyy HH:mm:ss",
                                          CultureInfo.InvariantCulture);

See the MSDN articles on standard date/time formatting and custom date/time formatting for more information.

However, if the purpose is really just to insert it into MySQL, you shouldn't convert it into a string at all. Just pass the parameter value straight into the appropriate MySQL command as a parameter. Adding string conversions just adds confusion. Wherever possible, keep data in its "natural" type - which in this case is DateTime.

people

See more on this question at Stackoverflow