executing SELECT statement with date in WHERE clause fails

Question

I'm building a dynamic query in my ASP.NET MVC project by the following:

Dim queryString As String = "SELECT VALUE userInfos FROM MyDBEntities.UserInformations AS userInfos"
If strWhere <> "" Then
    queryString = queryString & " WHERE " & strWhere
End If

' Call the constructor with the specified query and the ObjectContext. 
Dim SearchUsersQuery As New System.Data.Objects.ObjectQuery(Of UserInformation)(queryString, MyDB)

Dim lstOfUsers As List(Of UserInformation) = SearchUsersQuery.ToList

Where basically the strWhere is a string that I build up a dynamic filter depending on what the user selects to search on.

This works great until I need to add a date comparison to the date clause.

I'm trying the following:

strWhere = " userInfos.BirthDate <= " & StartDateForQuery.ToShortDateString

Which will end up as:

"SELECT VALUE userInfos FROM MyDBEntities.UserInformations AS userInfos Where userInfos.BirthDate <= 10/09/1992"

But when i try to execute the query with the ToList whenever a date is in the where string i get the following error:

The argument types 'Edm.DateTime' and 'Edm.Int32' are incompatible for this operation. Near WHERE predicate, line 1, column 103.

Any ideas on what my issue is?

Thanks in advance

Answer 1

You can't compare dates that way.

When you do this:

"SELECT VALUE userInfos FROM MyDBEntities.UserInformations AS userInfos 
 WHERE userInfos.BirthDate <= 10/09/1992"

10/09/1992 is being interpreted as an Int value.

Try putting single quotes around this value as in:

"SELECT VALUE userInfos FROM MyDBEntities.UserInformations AS userInfos 
 WHERE userInfos.BirthDate <= '10/09/1992'"

Probably you'll have to call a database date conversion function (depends on your database vendor) passing to it this date string.

Like this:

"SELECT VALUE userInfos FROM MyDBEntities.UserInformations AS userInfos 
 WHERE userInfos.BirthDate <= DataBaseSpecificToDateFunction('10/09/1992')"

The problem is that this query is being sent to the database and is the database server that'll execute it. That's why you need a database specific date conversion function.

For example: in Oracle we have the to_date function to convert a string to a datetime using a given pattern:

to_date('1998/05/31:12:00:00AM', 'yyyy/mm/dd:hh:mi:ssam')

In SQL Server we have the convert function as in:

convert(datetime, '2016-10-23 20:44:11',20) -- yyyy-mm-dd hh:mm:ss(24h)

More samples here.

Answer 2

Why don't you use the next code:

Dim queryString As String = "SELECT VALUE userInfos FROM MyDBEntities.UserInformations AS userInfos"
' Call the constructor with the specified query and the ObjectContext. 
'
Dim SearchUsersQuery As New System.Data.Objects.ObjectQuery(Of UserInformation)(queryString, MyDB)

'verify if should be a startdate for adding to query
'
If StartDateForQuery <> "" Then
 'add the condition to the query
    '

 SearchUsersQuery = SearchUsersQuery.Where("it.BirthDate  <= @BirthDate ")

 'add the parameter to be used
    '
 SearchUsersQuery.Parameters.Add(New ObjectParameter("BirthDate ", StartDateForQuery))
End If

Dim lstOfUsers As List(Of UserInformation) = SearchUsersQuery.ToList

You make use of the linq capabilities to generate queries (it "knows" how to generate the datetime parameter for DB query).

Check a sample here