robertkjr

robertkjr

  • NA
  • 61
  • 0

Time Zone, XML, C#, & Date of Birth (DOB)

Nov 29 2004 10:18 AM
I'm writing a pocket app, and I'm sending to the Pocket PC an XML file, that is data dumped from a SQL Server. The problem is that SQL Server has no just Date field. It is a Date Time Field. So when the DBA puts dates into the server, she uses Enterprise manager or something, and enters in dates with a Time of 00:00:00. However after taking that data and putting into XML form like so: //Members cmdStr = "SELECT " + "MemberID" + ",LName as Last" + ",FName as First" + ",SSN" + ",DOB" + ",Sex" + ",PlanMemID" + " FROM Members"; rsCmd = new OleDbCommand(cmdStr, cnn); dataAdapter = new OleDbDataAdapter(); dataAdapter.SelectCommand = rsCmd; dataAdapter.Fill(ds, "Members"); //Write out XML to file System.IO.StreamWriter xmlSW = new System.IO.StreamWriter("dump.xml"); ds.WriteXml(xmlSW, XmlWriteMode.WriteSchema); xmlSW.Close(); ds.Clear(); The Data in the XML file comes out like this: 120 TURNER SAMMIE 1937-12-17T00:00:00.0000000-05:00 M 414549283*01 - 121 YOUNG CHARLES 1938-05-30T00:00:00.0000000-04:00 M 408641109*01 See the TimeZone? And who knows why it seems arbitrary that some are marked -4 and some are marked -5. Now I try to read in this data on a device that is in Pacific time zone or (-7)... Do you see the problem? The date will come out being the day before! I may have found a work around but it seems like a bit of a fudge factor: dtPick.Value = Convert.ToDateTime(Convert.ToDateTime(rsMember["DOB"]).ToUniversalTime().ToShortDateString()); By the way... this example, I have converted the above XML into a dataset, and then put it into SQL Ce. Then I'm pulling it out in a record set called 'rsMember'. However this problem would still exist if I pulled directly from the XML. I convert to ToUniversalTime, and then take the 'ToShortDateString()'. If I just do 'ToShortDateString', it automatically conforms the date to the local time zone, which would put the DOB as a day before. So the question is, if you move from Australia to the US... are you 1 day younger? hehehehe. What is the proper way to format DateTime fields from a SQL Server database that we don't care what TimeZone it is?

Answers (1)