Timezone DOB gathering.

[robertkjr3d]

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:

<Members>
<MemberID>120</MemberID>
<Last>TURNER</Last>
<First>SAMMIE</First>
<DOB>1937-12-17T00:00:00.0000000-05:00</DOB>
<Sex>M</Sex>
<PlanMemID>414549283*01</PlanMemID>
</Members>
- <Members>
<MemberID>121</MemberID>
<Last>YOUNG</Last>
<First>CHARLES</First>
<DOB>1938-05-30T00:00:00.0000000-04:00</DOB>
<Sex>M</Sex>
<PlanMemID>408641109*01</PlanMemID>
</Members>

See the TimeZone?

And who knows why it seems arbitrary that some are marked -4 and some are marked -5. Probably something to do with daylight savings.

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?

 

[mrdenny]

Convert the datetime value to a varchar value in the select statement using format code 101.

select convert(varchar(10), DOB, 101),
...

This will output in standard US format MM/DD/YYYY.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[ESquared]

P.S., I thought Pacific was -8.

I second mrdenny's solution... use the convert function to trim the time off the datetime.

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[robertkjr3d]

It is...hehe