The data time and date time are incompatible in the operator equal to this one?

advertisements

I got this error after I tried to delete table row based on the date, start and end time. I used onClick function to pass the Date, start and end time from the table row. Then I used Ajax to send these values. Here is my code:

<cfoutput query="qryView">
    <table>
       <tr>
           <td>#dateFormat(DateSch,"mm/dd/yyyy")#</td>
           <td>#timeFormat(Stime,"hh:mm tt")#</td>
           <td>#timeFormat(Etime,"hh:mm tt")#</td>
           <td onClick="deleteDate('#dateFormat(DateSch,"mm/dd/yyyy")#','#timeFormat(Stime,"hh:mm tt")#','#timeFormat(Etime,"hh:mm tt")#')">Delete</td>
      </tr>
   </table>
</cfoutput>

<script>
    function deleteDate(DateSch,Stime,Etime){
        $.ajax(
        {
            type:'POST',
            url:'/AjaxFunction.cfc?method=deleteSchedule&returnformat=json',
            data:{'DateSch':DateSch,'Stime':Stime,'Etime':Etime},
            cache:false,
            success: function(data,x,xhrobj)
            {
                var Result = $.parseJSON(data);
                if($.trim(Result.STATUS) == '200'){
                    alert('Removed!');
                }else{
                    alert(Result.message);
                }
            }
        });
    }
</script>

And here is my delete Query:

<cfquery name="qryDelete" datasource="test">
    Delete From Table
    Where DateS = <cfqueryparam cfsqltype="cf_sql_date" value="#arguments.DateSch#">
    and Start = <cfqueryparam cfsqltype="cf_sql_time" value="#arguments.Stime#">
    and End = <cfqueryparam cfsqltype="cf_sql_time" value="#arguments.Etime#">
</cfquery>

First when I tried to click on the button delete I got an java script error like this:

SyntaxError: missing ) after argument list
deleteDate('74','{ts '2015-12-10 00:00:00'}','1970-01-01 08:00:00.0','1970-01-01...

Then I put dateFormat around the date value that I'm passing and timeFormat around time values. That fixed that issue. Then I got an error after my Ajax call:

"The data types time and datetime are incompatible in the equal to operator"

My values that I sent in my arguments look like this:

DateSch
12/10/2015
Etime
08:25 AM
Stime
08:00 AM

I do not know why I'm getting this error. I tried to convert these values with createDateTime function in coldfusion before I used them in the cfquery but that did not help. Does anyone know how I can fix this error/compare datetime with my values?


Always post the complete error message. The query you posted works fine with MySQL 5.6. So despite the "MySQL" tag, I have a strong suspicion you are actually using SQL Server, and that the full error message actually says:

[Macromedia][SQLServer JDBC Driver][SQLServer] The data types time and datetime are incompatible in the equal to operator.

That error is usually caused by submitting "time" values as "datetime" values. That causes an error because the query ends up trying to compare apples and oranges, ie time and datetime.

Some MS SQL Server drivers support disabling that behavior by adding sendTimeAsDateTime=false to the DSN connection properties. Unfortunately, that does not seem to work with the core Adobe Database Drivers. As you discovered, you will need to use a cast or convert. However, do not use varchar. That will result in implicit conversion, which can lead to wacky results in certain cases. Instead, use the same data type as on the left side of the operand, ie time

<cfquery datasource="theDatasource">
    DELETE FROM TableName
    WHERE  DateSch = <cfqueryparam cfsqltype="cf_sql_date" value="#arguments.DateSch#">
    AND   STime = CAST(<cfqueryparam cfsqltype="cf_sql_time" value="#arguments.Stime#"> AS TIME)
    AND   ETime = CAST(<cfqueryparam cfsqltype="cf_sql_time" value="#arguments.Etime#"> AS TIME)
</cfquery>