0

I am trying to save a DateTime to a database.

When I run the code from my server machine and save the date to the db, it saves the DateTime correctly, but when I deploy the site to production, access it from my local machine, and try to save the same DateTime, then it changes it to a diferent date and time. I guess this may be due to diferent timezones on my machine and the server machine.

I tried to convert the datetime to UTC but its not working:

DateTime OnlyStartDate = Convert.ToDateTime(app_date);
DateTime NewStartDateTime = Convert.ToDateTime(
    OnlyStartDate.Add(TimeSpan.Parse(appData.start.ToString("HH:mm:ss"))));         
startTime = Convert.ToString(NewStartDateTime);

This is the query in which the variable startTime is passed, which saves the wrong time to the db:

query = "Insert into [Appointments] Values('" + loc_id + "', '" + appData.titleId + 
    "', '" + patiant_id + "', '" + app_date + "', '" + startTime + "', '" + endTime + 
    "', '" + appData.providerId + "', 'enable', 'False', '" + userEmail + "', '" + 
    DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "', '" + userEmail + "', '" + 
    DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "', '" + 
    appData.statusId + "', '" + appData.userId + "','')";

DB.executeQuery(query);
Rufus L
  • 36,127
  • 5
  • 30
  • 43
BILAL AHMAD
  • 11
  • 2
  • Welcome to Stack Overflow. I'd strongly advise you to start using parameterized SQL as the *very first* thing to do. That will avoid conversion issues and SQL injection attacks, as well as making the code much more readable. I'd also advise you to do as few conversions between strings and date/time values as possible. – Jon Skeet Apr 18 '18 at 20:00
  • "not working" is not helpful only itself. What do you mean by that? You get an error? Exception? Unexpected result? And do **not** store your DateTime values as a string. Store them exactly what they are. Read: https://sqlblog.org/2009/10/12/bad-habits-to-kick-choosing-the-wrong-data-type – Soner Gönül Apr 18 '18 at 20:00
  • I agree with @DaisyShipton. And would add, you want to keep it as a DateTime type all the way to the SQL query entry. Also check out... https://msdn.microsoft.com/en-us/library/system.datetime.touniversaltime(v=vs.110).aspx – mrdnk Apr 18 '18 at 20:01
  • Thanks @DaisyShipton thanks for suggestions but I dint wrote this whole applications i am just asigned a small tweak in it about datetime conversion issue on which i am stuck at – BILAL AHMAD Apr 18 '18 at 20:04
  • You don't need to rewrite the whole application just to use SQL parameters for this - although you should probably raise the issue if it's like this everywhere. (Although you haven't told us what the type of the column is on the database, which doesn't help us.) – Jon Skeet Apr 18 '18 at 20:05
  • @SonerGönül The date i passed to startTime is this "2018-05-10 01:00:00.000" and the date which it saves to the db is "2018-05-10 20:00:00.000" where as i want to save the date to as it is as i passed but it converts it to some other time zone i guess – BILAL AHMAD Apr 18 '18 at 20:07
  • the db column type is datetime – BILAL AHMAD Apr 18 '18 at 20:08
  • "but it converts it to some other time zone i guess" - rather than guessing, log *everything*. At the moment we hardly know anything - we don't know what time zone your server is in, what all the values are, how you're looking at the value in the database, or even what database you're using. With so little information, it's *very* hard to help. – Jon Skeet Apr 18 '18 at 20:18
  • @DaisyShipton i am using sql db,i am in pakistan my server is in US chicago – BILAL AHMAD Apr 18 '18 at 20:28
  • Have you tried using UTC time instead? `DateTime.UtcNow.ToString()` and if `app_date` is a local time: `Convert.ToDateTime(app_date).ToUniversalTime();`. – Rufus L Apr 18 '18 at 20:29
  • By "sql db" do you mean SQL Server? There are lots of databases that use SQL... please edit *all* the relevant information into your question. – Jon Skeet Apr 18 '18 at 20:29

2 Answers2

1

Working with DateTimes is tricky and there are rules that you should follow to ensure that the system behaves as expected.

  • Always transmit the UTC value of a DateTime.
  • Always transmit the value of a DateTime using ISO8601 notation if the serialization of the instance occurs using a string representation. Sending DateTime using json or in a query string in a URL are both examples where this should be done.
  • Always store DateTime instances using the UTC value.
  • Always store DateTime instances using a native type in the persistence store (never as string).
  • When sending DateTime values from the client convert to a UTC value as early as possible as the client "knows" about it's relevant time zone.
  • When sending DateTime values from the server to the client convert to the local time zone as late as possible on the client as the client "knows" about it's relevant time zone.
  • When displaying a DateTime value convert to a string (from DateTime type) as late as possible in the call stack (ie. it is a presentation layer concern best handled by the client).
  • When sending a DateTime value from the client back to the server convert to a DateTime type as early as possible.
  • When creating ado.net commands/queries (regardless of the underlying provider) always use parameters for all values and in the parameter:
Igor
  • 60,821
  • 10
  • 100
  • 175
0

Use DateTime.UtcNow when saving data into db, on display convert it to the timezone you want to display datetime with, this is the main practice initially.

example for displaying UTC datetime in Jordan timezone:

TimeZoneInfo timeZoneInfo = TimeZoneInfo.FindSystemTimeZoneById("Jordan Standard Time");
string CreatedDate = TimeZoneInfo.ConvertTimeFromUtc(model.CreatedDate, timeZoneInfo).ToString("d/M/yyyy HH:mm:ss")

model.CreatedDate is a UTC datetime from db.

Saeed Al Hamed
  • 1
  • 1