Issue with dates and times

Submitted by feltron_cq on 2016-03-22

Still having problems.

I added a new comment to the end of
webissues.mimec.org/node/840 but I'm not sure it's been seen yet. Certainly no response yet.
Would value some assistance please.

Hello,
Yes, sorry, I must have missed it. The query looks fine, at least I can run it on my system without errors. Perhaps one of the values stored in your database is incorrect, and it cannot be converted to a date, but it's hard to say just from the error message.

Regards,
Michał

I think the issue might be that we are using the European date format yyyy-mm-dd instead of the US format yyyy-dd-mm.

When I do 

SELECT COUNT(*)
  FROM [webissues].[dbo].[attr_values] where attr_id=15 and CONVERT(datetime, attr_value, 101) < '2016-02-29 00:00:00'

I get an error

but if I do 

SELECT COUNT(*)
  FROM [webissues].[dbo].[attr_values] where attr_id=15 and CONVERT(datetime, attr_value, 101) < convert(datetime,'2016-02-29 00:00:00',101)

I don't get an error.

This is a simplified version of the query being run, but I think it's the root of the problem.

You may try changing the makeDateCondition() function in System_Api_QueryGenerator. The $placeholder would have to be replaced with convert(datetime,$placeholder,101) if I'm not mistaken.

Regards,
Michał