Problem with date time

Hi,
I have these codes
                    string str2 = DateTime.Parse(str.Substring(0, pos1) + " 15:30:00").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);

Open in new window

to change the records on Mysql DB, but after that, the date time is still
2015-04-01 03:30:00

which is further shown as "AM". why?
LVL 12
HuaMin ChenProblem resolverAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David Johnson, CD, MVPOwnerCommented:
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.
Jacques Bourgeois (James Burger)PresidentCommented:
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.
Jacques Bourgeois (James Burger)PresidentCommented:
@David

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.
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

HuaMin ChenProblem resolverAuthor Commented:
Jacques,

I have these
                    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);

Open in new window


but why the datetime column is stored as
2015-04-01 03:30:00

that is AM, while I expect to have PM. how to adjust the codes?
Gustav BrockCIOCommented:
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);

Open in new window

/gustav

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jacques Bourgeois (James Burger)PresidentCommented:
Look at what I suggested

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

and what Gustav suggested

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.
HuaMin ChenProblem resolverAuthor Commented:
Gustav,

I have these
                    string str2 = DateTime.Parse(str.Substring(0, pos1) + " 9:30:00").AddHours(6).ToString("yyyy'/'MM'/'dd HH':'mm':'ss");

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

                    DBUtil.ExecuteNonQuery(conn, sql);

Open in new window


but I still get the same problem.
Gustav BrockCIOCommented:
No, it won't work as I and Jacques try to explain.

Use code similar to what I posted above.

/gustav
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.