InfoPath Dev

I'm not sure if this is where to start but I'm having an issue with displaying a datetime in an infopath 2013 form. Little background: this is an infopath 2013 form that is saved to a network location for users to enter data. My main connection is a connection to SQL 2017. I have a smalldatetime column on the SQL table called referralDateTime (smalldatetime; "2020-01-03 07:45:00". On my infopath form I have a text box/ready only that the returned date populates. On the form a user enters a date range on the form, I pass those values using c#, then call a stored procedure which returns data based on the parameters I'm passing. This all works perfectly. However, when the referralDateTime is displayed on the infopath form, it displays the value as "3/1/2020 07:45:00 AM" but has the dotted red lines around the box. I have tried formatting the date with SQL to match that format in infopath, but still red lines. I am unsure how to format this to avoid the red lines on the text box. Here is the c# code that I'm using currently that does return the datetime value to the user screen: // Reading in values from Stored Proc SqlDataReader dataReader; SqlDataAdapter da = new SqlDataAdapter(_oCommand); DataSet ds = new DataSet(); da.Fill(ds, "RetrieveData_View"); dataReader = _oCommand.ExecuteReader(); // Create a navigator for the xpath doc or set of XPathNavigator nav = MainDataSource.CreateNavigator().SelectSingleNode("/dfs:myFields/dfs:dataFields/d:TestReferrals", NamespaceManager); XPathNavigator RetrieveData_View = nav.Clone(); MessageBox.Show("Please wait while data is retrieved...", "Referral Submission", MessageBoxButtons.OK, MessageBoxIcon.Information); // This will read / loop through data being returned from Stored Procedure while (dataReader.Read()) { Global.dateTimeOfReferral = dataReader["DateTimeofReferral"].ToString(); RetrieveData_View.SelectSingleNode("/dfs:myFields/dfs:dataFields/d:TestReferrals/@DateTimeofReferral", NamespaceManager).SetValue(Global.dateTimeOfReferral); RetrieveData_View.InsertAfter(RetrieveData_View); } // END OF WHILE LOOP // Deleting duplicate row retrieved XPathNavigator firstNode = MainDataSource.CreateNavigator().SelectSingleNode("/dfs:myFields/dfs:dataFields/d:TestReferrals[1]", NamespaceManager); firstNode.DeleteSelf(); dataReader.Close(); cnn.Close();

 XML date times look like this: 2020-03-09T12:31:22

When you set the field, you may just need to replace the space in your SQL date with a T. 

Hilary - Thank you for getting back to me. So in my stored procedure I am now returning the smalldatetime which is currently set in the DB as '2020-03-13 08:42:00' and so I am now returning the date as an nvarchar -> '03-13-2020 8:42:00 AM' but when it returns the date to the Date Time field on the form it does show the datetime as '03-13-2020 8:42:00 AM' but still has the red dotted lines. The date is being returned to a text box and I've checked the formatting on the field I have selected "None (display XML value)" and I have also selected "Display the date like this: *3/14/2001" with the time as "*9:46:55 AM". The data type on the field under the data tab is "optional_dateTime.notz". I have also tried returning the date from SQL as '03-13-2020T8:42:00 AM' with still the same result. I am not sure what to do at this point?

You need HH. So, just adding the "T" is not enough, you need a 0 before the hour. Return it from SQL like (replace getdate function with your column): SELECT FORMAT (getdate(), 'yyyy-dd-MMTHH:mm:ss')

Thank you. So I have edited my SQL statement to read like so: format(DateTimeField, 'MM/dd/yyyy HH:mm:ss'), format(DateTimeField, 'MM/dd/yyyyTHH:mm:ss'), format(DateTimeField, 'yyyy/dd/MM HH:mm:ss'), format(DateTimeField, 'yyyy/dd/MMTHH:mm:ss'). These all when returned show as I specify but still have the red dotted lines. I don't understand what I'm doing wrong. If I said the function on the field to now() it displays the date in the text field as 'MM/dd/yyyy HH:mm:ss'. Should I be doing something to the datetime field within my c# code before pushing it back to the form??

I don't understand why you formatted your date time to use a slash when you need to use the dash, as per all my earlier posts. Or why you are using a different format than what I suggested. As I've said, you need to format in a date time format that XML uses, which is yyyy-MM-ddTHH:mm:ss

Edited to add a sample date time - using today's date: 2020-03-19T10:02:05

My apologies, I had mistyped the last two formats I had tried which were: format(DateTimeField, 'yyyy-dd-MMTHH:mm:ss'), format(DateTimeField, 'yyyy-dd-MMTHH:mm:ss'). In your previous response you had the date as yyyy-dd-MM but you just suggested 'yyyy-MM-dd' so I did swap the month and day and that did work. So thank you for your help its returning without the red lines now! Much appreciated!

I have 1 more question - if you would be able to help me out. I'm not finding much luck online and I am running out of options on how to make this work. I created another post about publishing to a network drive. I'm not sure how you search the forum for my question as I am very new to the website but my post is called "Publishing and Infopath form to network location" - again I'm not sure what to do :(

Yup, you are right - I mistyped those! So the easy way to find out how InfoPath stores data is to add a date time control to a form, fill it out, and open the saved XML with a text editor. I'll see if I can help w/ your other question.