0

I have an ASP.NET application with a WCF service giving users access to a table on a SQL 2005 database.

Users are able to filter using int and Boolean fields etc. however, they are unable to filter using datetime fields. The page returns a HTTP 500 Internal Server Error. I have tried using the various OData datetime search formats and tried versions 1, 2 and 3 of OData but the server still will not respond.

I tested using a development copy of the database on a SQL 2012 box and datetime searches work. So of Couse I assume that the problem is with how datetime fields are processed/stored in SQL 2005. Am I correct in this assumption? Is this a known issue? Is there any way to resolve other that upgrading the SQL version?

flowagss
  • 25
  • 8
  • 500 means the *server* failed. A bad format would return a 4xx error. Check the WCF service's logs for exceptions. BTW OData has no problem with DateTime, provided you *do* use DateTime in the database fields and DTOs. The proxy will also use DateTime. Why are you using strings at all? Even in the URL, the ISO8601 format should work – Panagiotis Kanavos Oct 21 '16 at 13:00
  • You may need to run the WCF service in debug or modify it to provide more diagnostic information as to what caused the HTTP 500 Internal Server Error. Just knowing that a HTTP 500 error was generated is not enough to diagnose this problem. – JohnH Oct 21 '16 at 13:06
  • Is the problem http://stackoverflow.com/questions/25189557/how-to-get-web-api-odata-v4-to-use-datetime? @Panagiotis: Not true. OData V4 does not natively support DateTime, only DateTimeOffset. – Marvin Smit Oct 24 '16 at 07:28
  • @MarvinSmit which misses the point. The *proxy* will be strongly typed. The OP shouldn't have to try *any* string formats, ONLY the ISO8601 format should be used. Even then, the error wouldn't be 500. There is definitely a bug in the service code. In fact, the last paragraph suggests that the OP tried to use different data types with the same DTO – Panagiotis Kanavos Oct 24 '16 at 07:35
  • @flowagss there is no difference between 2005 and 2008+. Most likely, you tried to load *different* types and got a conversion error in your own code. SQL Server 2005 didn't have a datetimeoffset type. Your code was probably built to work against a table with a `datetimeoffset` field and failed when you tried to use it with a table that has a different type, ie `datetime`. – Panagiotis Kanavos Oct 24 '16 at 07:37
  • BTW SQL Server 2005 isn't simply unsupported by now. It's ancient. The earliest supported SQL Server version is 2012. – Panagiotis Kanavos Oct 24 '16 at 07:38
  • The field in the database is a datetime field not a string. Agreed, 2005 is ancient, we are in the process of upgrading the server but I was hoping to get this working in the meantime. Thanks of the info about SQL server 2012 being the earliest supported version I had not seen that. – flowagss Oct 24 '16 at 15:45

0 Answers0