[Adan]

Getting problem with date time in Mysql DB with C# .NET Programming ?

string str2 = DateTime.Parse(str.Substring(0, pos1) + " 9:30:00").AddHours(6).ToString();

                    sql = "update package set sms_time = STR_TO_DATE('" + str2 + "','%e/%c/%Y %H:%i:%s') "
                        + "where id = " + id.Text + " ";

                    DBUtil.ExecuteNonQuery(conn, sql);

The date time column is stored as
2015-08-025 03:30:00

that is AM, while I expect to have PM. how to adjust the codes?

[Ganesh]

A date/time is stored internally as a double (epoch + x seconds) what do you want to display? Just the date? A datetime is usually displayed in your user-culture i.e. regional settings.

[Abhey]

ToString follows the format defined in the Control Panel.

It is usually the way to display the date on the screen, because it then shows as the user is used to see dates on his computer.

But it is a very bad way of saving the date in a database, a file, or any data repository, because you will get an inconsistent format between users and some users might have problem reading back the date.

In fact, why do you continually convert the date back and forth?

You first convert it from a string (str) to a date (Parse). Then back to a string (ToString). Then back to a date (STR_TO_DATE).

What you should do is convert it to a date straight from the start with the Parse, and then leave it as a date for all your operations. Something like the following. I cannot give you the SQL, because I do not know MySQL and the way it handles dates in its SQL.

datetime dt2= DateTime,Parse(str.Substring(0, pos1).AddHours(15).AddMinutes(30);

If your problem is the display, then you can force the format with by using the ToString overload that enables you to specify the format instead of the standard ToString that follows the Control Panel: https://msdn.microsoft.com/en-us/library/zdtaw1bw%28v=vs.110%29.aspx

It is not recommended to force the display on the screen however, where it should follow the Control Panel, which is the format that the user sees everywhere. You should force the format only when required for reports, files, etc.

[Ganesh]

In .NET, datetime is a Long, not a Double as it was in COM. It is the number of ticks since 1/1/1, a tick being one hundred nanoseconds.

[Abhey]

As stated already, the value is not stored like that. It is simply the default display format of the stored value.
Further, always handle date and time as date and time, never strings, no exceptions.

Thus, for your SQL, do something like this where you format the date value as a proper 24-hour string expression for MySQL:

DateTime smsTime = DateTime.Parse(str.Substring(0, pos1)).Add(new TimeSpan(9, 30, 0)).AddHours(6);
string sqlTime = smsTime.ToString("yyyy'-'MM'-'dd HH':'mm':'ss");

sql = "update package set sms_time = '" + sqlTime + "' where id = " + id.Text + "";

DBUtil.ExecuteNonQuery(conn, sql);

[SapnaVishwas]

datetime dt2= DateTime,Parse(str.Substring(0, pos1).AddHours(15).AddMinutes(30);

DateTime smsTime = DateTime.Parse(str.Substring(0, pos1)).Add(new TimeSpan(9, 30, 0)).AddHours(6);

We record the result of the Parse in a datetime, not in a string as you do with your str2. And then we do all the operations through datetime methods, not string operations +” 9:30:00″. That way we are completely independent of the format, that might be different from one computer to another, from one database to another. When treating date and time through strings, you cannot be sure of the results because it depends on interpretation of the string, and this can be different depending on the environment.

How do you get 2015-04-01 03:30:00? Is this something that you get from some tool or from some code? It might be recorded as 15:30, but displayed with code that prevents the “PM” from showing.

Also, have you checked what is in str? What is the value of pos1? Did you verify that the date you get with your Substring does not already contain a time portion? Have you checked the content of str2 before building your SQL to make sure that the problem is not there instead?

When dealing with such problems, you need to use the debugger on all the values that you are dealing with in order to know where the thing starts to give incorrect results.