Filter External Content Type by Date

Question

I am creating a BCS connection to a SQL database with thousands of records. I'd like filter Read List method to include items from a specific date. I have a field in my database table called theDate with a datatype of Date. I'm struggling with the parameter to set in SharePoint Designer, however. I created a filter parameter with the following settings:

• Filter Type: Comparison
• Operator: Equals
• Filter Field: theDate

After saving and settings permissions, I added a Business Data List to a page, connected it to the External Content Type.  I enter a date and I get the following error:

An error occurred while retrieving data from [Database Name]. Administrators, see the server log for more information.

Checking the ULS log, I see this:

System.InvalidOperationException: There was an error ('The DateTime value given has Kind set as 'Unspecified'. It cannot be converted to a Kind that is expected by the external data source.') applying the Interpretations defined on the TypeDescriptor to the value. The TypeDescriptor is named 'theDate' and is defined on Parameter with name '@theDate' of Method with name 'Read List' on Entity (External Content Type) with name 'ExtractLog' and namespace 'spextra'. ---> System.ArgumentException: The DateTime value given has Kind set as 'Unspecified'. It cannot be converted to a Kind that is expected by the external data source.   
 at Microsoft.SharePoint.BusinessData.Infrastructure.DateTimeNormalize.ReverseApply(Object& value)   

How do I fix this?

Answer 1

Hi,

This exception can be caused by incorrect date format entered in the filter field of Business Data List. Please try this format to enter (for example 2010-04-01 00:00:00Z). Look at the picture of my example.

 

---

Dmitry

Lightning Tools Check out our SharePoint tools and web parts

Answer 2

You can try theDate.ToLocalTime()

I used Telerik's DateTimePicker control and it worked for me

---

Merin