InfoPath Dev

My SQL data type is smalldatetime.
My Web Service dataset type is datetime.

My data looks like this:
"11/9/2004"

My SQL statement looks like this:
DATEADD(hh,13,tEOR.AnticipatedStartDateAndTime) AS AnticipatedStartDateAndTime,

My XML Web Service data looks like this:
<AnticipatedStartDateAndTime>2004-11-09T13:00:00.0000000-05:00</AnticipatedStartDateAndTime>

My data ends up displaying in InfoPath as 8:00 (13 minus 5).
My actual data should reflect only the "13:00:00", I'm not sure where the "05:00" is coming from. Possibly Grenwich time or something.

Does this have something to do with my regional time settings or something? If so, where do I need to go and change this at.


SOLUTION: Since this is a known bug...viahttp://blogs.msdn.com/dareobasanjo/archive/2004/04/14/113179.aspx
we'll need to do a work-around. First, within the Web Service, keep your type as DateTime and do NOT change it to string, since (hopefully) a fix will come sometime soon (who knows when).
Second, if you are developing the InfoPath form as a project in Visual Studio .NET you're in luck, if not, you'll need to convert the following code to script.

In my case, within one of the OnAfterChange events I have the following code: Note: this is C#, I've also converted it to VB.Net
so let me know if you need that code too.

IXMLDOMNode myNode1 = thisXDocument.DOM.selectSingleNode("/dfs:myFields/dfs:dataFields/s0:SelectEmploymentResponse/s0:SelectEmploymentResult/ns1:DataSet1/ns1:dbo_udp_SelectEmployment/ns1:MyDateAndTime");
GetDateAndTimeFromNode(myNode1);

This will need to be done for every datepicker on the form.

Here's the routine for GetDateAndTimeFromNode():

public void SetDateTime(IXMLDOMNode myNode) {
int NOONORMIDNIGHT=12;
string myDate = myNode.text.Substring(0,10);
string myTime = myNode.text.Substring(11, 8);

if(myNode != null)
{
string ifbefore10am="";
int remainderoftimestringafterhour=0;
//not sure of how to get military time, so I'll add 12 hours, unless the time is 12, then I'll subtract 12
int ihr;
if (String.Compare(myTime.Substring(2,1), ":") == 0)
{
remainderoftimestringafterhour = 2;
ihr = Convert.ToInt32(myTime.Substring(0,remainderoftimestringafterhour));
} else {
remainderoftimestringafterhour = 1;
ihr = Convert.ToInt32(myTime.Substring(0,remainderoftimestringafterhour));
}

if (String.Compare(myTime.Substring(myTime.Length - 2, 2), "PM") == 0)
{
if (String.Compare(myTime.Substring(0,2), Convert.ToString(NOONORMIDNIGHT)) != 0) {ihr+=NOONORMIDNIGHT;}
}
else if (String.Compare(myTime.Substring(0,2), Convert.ToString(NOONORMIDNIGHT)) == 0)
{
//do something - MIDNIGHT
ihr = 0;
ifbefore10am = "0";
} else {
//not MIDNIGHT, but before 10:00:00 AM
if (ihr < 10) {ifbefore10am = "0";}
}
myNode.text=myDate + "T" + ifbefore10am + Convert.ToString(ihr) + myTime.Substring(remainderoftimestringafterhour,6);
}
}

This conversion is done because the DatePicker expects the Time to be in a military time format.

There may or may not be a few declaration issues, since I've kinda piece together the code, but it should be fairly easy to remedy.

Hope this helps...

Thanks for sharing your solution with us!

Greg Collins [InfoPath MVP]

Sakieboy, could you post the VB.NET code you refered to above? It looks like a fix I'd like to try.

Thanks!