CODEWITHSUNDEEP
c#azure-sql-database
Max
Max

Reputation: 25

DateTime.Now.ToString() timezone issue

I upload a web-form in Azure-App Services, which link in to Azure-Sql database.

When user create a record, the aspx.cs generate a string and store in SQL server.

cmd.Parameters.AddWithValue("@ModifyDate", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));

When i run the code locally, the date+time works correctly. But when I run it in URL, it seems the new records are always 8 hours late. (exp: i save at 2019-5-23 15:00:00, SQL record became 2019-5-23 7:00:00) Since i was locate in Taiwan (GMT +8) I knew it has something to do with timezone.

Usually I'll spend hours testing the code, but not this time since the bug can't be fix locally, and publish app multiple times are time consuming.

sql = ("INSERT INTO MOrder (OrderDate, BranchID, SupplierID, ModifyDate) VALUES (@OrderDate, @Branch, @Supplier,@ModifyDate)");
            using (SqlCommand cmd = new SqlCommand(sql, cnn))
            {
                cmd.Parameters.AddWithValue("@OrderDate", datebox.Text);
                cmd.Parameters.AddWithValue("@ModifyDate", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                cmd.Parameters.AddWithValue("@Branch", Session["Gbranch"].ToString());
                cmd.Parameters.AddWithValue("@Supplier", Session["Gsuply"].ToString());
                cmd.ExecuteNonQuery();
            }

Can someone modify my code so the Timestring can be stored properly? Thank you.

Upvotes: 1

Views: 877

Answers (2)

Max
Max

Reputation: 25

I tried DateTime.Now / DateTime.Now.ToUniversalTime / DateTime.UtcNow and apparently they all generate same value. (always in GMT +0 , despite your current timezone)

I solved this problem by manually add 8 hours on top of it.

Here is my modified code (I am in Taiwan, GMT +8)

cmd.Parameters.AddWithValue("@ModifyDate", DateTime.UtcNow.AddHours(8).ToString("yyyy-MM-dd HH:mm:ss"));

Thank you all for helping / recommending.

Upvotes: 0

Muhammad Nasir
Muhammad Nasir

Reputation: 2204

Change you time to UTC before converting it to string. Change this code:

cmd.Parameters.AddWithValue("@ModifyDate", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));

to

cmd.Parameters.AddWithValue("@ModifyDate", DateTime.Now.ToUniversalTime().ToString("yyyy-MM-dd HH:mm:ss"));

when you read date string from server you need to Convert it back local time as follow.

TimeZone.CurrentTimeZone.ToLocalTime(Convert.ToDateTime("UTC Date time string"))

Upvotes: 1

Related Questions