CODEWITHSUNDEEP
mysqlcoldfusion
espresso_coffee
espresso_coffee

Reputation: 6110

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

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?

Upvotes: 1

Views: 5559

Answers (3)

Thuan Tran
Thuan Tran

Reputation: 437

Add ";sendTimeAsDateTime=false" to dataSource config if you are using spring mvc framework Example :

<property name="url"
        value="jdbc:sqlserver://localhost:1433;databaseName=testdb;encrypt=true;trustServerCertificate=true;sendTimeAsDateTime=false" />

Upvotes: 0

Leigh
Leigh

Reputation: 28873

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>

Upvotes: 2

Darwin von Corax
Darwin von Corax

Reputation: 5246

Try something like this:

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

I'm not familiar with ColdFusion, so I can't say for sure that the preceding is free of syntax errors. Additionally, you may have to change your date format to YYYY-MM-DD to keep MySQL happy.

Upvotes: 0

Related Questions