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?