Filter an External List on a datetime Field

Question

So I have an external list in SharePoint 2010 that connects to a SQL database and pulls the data successfully. I also have 2 filters defined along with a limiting (TOP) filter. The first filter is for a column that contains integers and works just fine. The second filter is on a datetime column and does not work for any intuitive input from the user. The filter is defined (using SPD) on the Read List operation as a Greater Than Comparison filter.

After much research I found that the In Parameter defined in the model is set to UTC. Sure enough, if I use 2012-01-01Z it works. What is the best way to changen this? Can it be done from SPD or do I have to customize the model? My users would prefer mm/dd/yyyy format or similar.

Answer 1

The best way to change this is to modify the default view for the external list.

Find the date FieldRef in this XMLDefinition and you can add a format element to it.

The format mm/dd/yyyy is not an option however, as defined in the MSDN here.

For date/time columns, the following values are possible:

DateOnly Only display (and allow edits to) the date portion (the time portion is set to 12:00 A.M. for all edited values).

DateTime Display and edit both date and time of day (default).

ISO8601 Display date and time in ISO8601 time format converted to UTC: YYYY-MM-DDTHH:MM:SSZ.

This is the format used for document properties in the Microsoft Office system, as well as for the standard interchange format used in Microsoft SharePoint Foundation between New and Edit forms and the server.

ISO8601Basic Use the abbreviated form of 8601 date/time formats: YYYYMMDDTHHMMSSZ.

This is the format used for vCard/iCal.

Answer 2

I could find no SPD solution for this.

My solution was to edit the XML definition and add a Convert function to the corresponding filter variable in the SQL code. In this case, my input Parameter (<Parameter Direction='In' Name='@USRDAT02'>) which was connected to my Filter Descriptor (<FilterDescriptor Type='Comparison' FilterField='USRDAT02' . . .) showed up in the SQL command (<Property Name='RdbCommandText' . . .) as the variable @USRDAT02. I simply replaced all occurances of @USRDAT02 in the SQL with CONVERT(DATETIME, @USRDAT02) and it worked. This forces the SQL engine to figure out how to translate the string entered by the user into a valid date for comparison.