Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

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.

share|improve this answer
    
My problem is in the pre-query filtering of the ECT List web part, Jon. When you add the web part to the page, the view can be modified (select the option to retreive all items specified by the user) to display a filter header. This is where I was having trouble. –  Toadmyster Jan 24 '12 at 0:18
    
Ah, I see. thanks for the clarification. In that case your solution seems valid. –  Jon Reid Jan 24 '12 at 14:11
up vote 0 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.