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

Question

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

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.

There are answers for this already: [Example 1](http://stackoverflow.com/a/6496292/724591), [Example 2](http://stackoverflow.com/questions/3023649/hour-from-datetime-in-24-hours-format) — matth, Nov 18 '13 at 05:13
possible duplicate of [Datetime in 24 hour format](http://stackoverflow.com/questions/6496255/datetime-in-24-hour-format) — marc_s, Nov 18 '13 at 05:55

score 6 · Accepted Answer · answered Nov 18 '13 at 05:13

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.

C Sharper · Answer 2 · 2013-11-18T05:26:06.463

1

Make following line:

s_added_date = (dr["s_added_date"].ToString());

To

s_added_date = (dr["s_added_date"].ToString("dd/MM/yyyy HH:mm:ss"));

Your code will be:

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("dd/MM/yyyy hh:mm:ss"));
        s_added_by = (dr["s_added_by"].ToString());
    }

If ypu want it as 11-Nov-2013 10:23:25 format:

 s_added_date = (dr["s_added_date"].ToString("dd-MMM-yyyy hh:mm:ss"));

edited Nov 18 '13 at 05:26

answered Nov 18 '13 at 05:10

C Sharper

8,284
26
88
151

@JonSkeet but in that case we need not write hh? coz i refered your answer only : http://stackoverflow.com/questions/15837609/datetime-value-tostringformat-gives-me-12-hour-clock?answertab=oldest#tab-top – C Sharper Nov 18 '13 at 05:15
I don't understand what you're asking. `hh` is 12 hour, `HH` is 24 hour. The OP wants a 24-hour format, so he should have `HH`... although I don't believe he should actually be formatting the value at all. – Jon Skeet Nov 18 '13 at 05:17
@JonSkeet he has 24 hours format, and wants to be in 12 hours format, thats what i understands from question – C Sharper Nov 18 '13 at 05:21
I suggest you reread the question. He's seeing 24 hour format in SQL Server (in some form), but 12 hour format in his application: "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." – Jon Skeet Nov 18 '13 at 05:23
@JonSkeet ohh, yeah , sorry, was totally my mistake in reading and understanding the question, thanx a lot – C Sharper Nov 18 '13 at 05:26
@F.R.I.E.N.D.S. when I use 's_added_date = (dr["s_added_date"].ToString("dd/MM/yyyy HH:mm:ss"));' I am getting error that 'No overload for method 'ToString' takes 1 arguments' – Ashok Nov 18 '13 at 06:50

score 1 · Answer 3 · answered Nov 18 '13 at 05:33

Try this

SqlCommand cmd = new SqlCommand("SELECT TOP (1) s_name, s_city, s_address, DATE_FORMAT(s_added_date,'%T'), 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());
    }

if have problem with format check this link http://www.w3schools.com/sql/func_date_format.asp — Raj Mohan, Nov 18 '13 at 05:34

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

3 Answers3