This article describes how to display a “Date” or a “Date and Time” string, retrieved from a SharePoint List or Library, in an InfoPath 2007 Text Box Field set to Date only / Date and Time type.
You have created a SharePoint 2007 environment and you have a Document Library or a Custom List in one of your Site Collections. In this library you have created a new Date and Time column and you have set the display to Date only and you have added an item in the library.
In your InfoPath 2007 template you create a data connection to retrieve data from the SharePoint Library. You create a Text Box with the Data Type Date (date) and with a certain Date Format and you assign directly the retrieved value to the Text Box. If you preview the InfoPath 2007 form you will receive an warning for the field - "Only date allowed". You will receive a similar message when you have a Date and Time field (Only date and time is allowed).
This is happening because by default the SOAP response from the SharePoint environment contains the date in SQL format (eg: PartialXmlTag: <z:row ows_ColumnName='2009-10-16 00:00:00' ...>) and the Text Box is expecting a date without the time part or a date and time formatted like this: YYYY-MM-DDThh:mm:ss
In order to bypass this design you need to the following:
1. For the Date only type of field in InfoPath:
When assigning the value to the Text Box field you need to use the “substring” function in order to provide only the date part.
Example: substring(@ColumnName, 1, 10) where ColumnName is the column retrieved from the SharePoint Library/ List
With this we receive a string like 2009-10-21 00:00:00 and we are converting it to 2009-10-21. The result is a valid date string for the Date only field.
2. For the Date and Time field type
You should use the “translate” function in order to format the text received from SharePoint 2007.
Example: translate(@ColumnName, “ ”,”T”) Where ColumnName is the column retrieved from the SharePoint Library / List
With this we will receive a string like 2009-10-21 00:00:00 and we will convert it to 2009-10-21T00:00:00 that is a valid string for the Date and Time field.