0

I have od_bulk table my InDate column defined as DATE.

Running the following query

SELECT IFNULL(InDate, CURDATE()) ReturnDate FROM od_bulk

generates the following error message

Type of value has a mismatch with column typeCouldn't store <2/27/2012 12:00:00 AM> in IFNULL(InDate, CURDATE()) Column. Expected type is MySqlDateTime.

Using CAST is the only way to get it executed

SELECT CAST(IFNULL(InDate, CURDATE()) AS DATE) ReturnDate FROM od_bulk

Is this an expected behavior?

Update: Thanks for all the tips. This seems to be a glitch in DevArt's myDAC connector. I tested the table/code using phpMyAdmin and it works as expected without any casting

Garik M
  • 1
  • 2
  • 1
    I can't reproduce it. Could you post the od_bulk CREATE statement? – Uueerdo May 12 '15 at 16:50
  • Use `NOW()` insted of `CURDATE()` – Mihai May 12 '15 at 16:53
  • That's not an standard mysql error. what client/app are you using to run this query? at best mysql would say "expected datetime", not "mysql expects mysqldatetime" - that'd just be redundant. – Marc B May 12 '15 at 16:54
  • @MarcB Good point, that looks like an error you'd get from a library supporting parameterized queries. – Uueerdo May 12 '15 at 16:56
  • I'm testing the code in dbForgeStudio for MySQL. The actual code is running in Delphi application using DevArt MyDAC direct connection to mySQL server 5.1.45-community. – Garik M May 12 '15 at 17:06
  • It's not a table structure or CURDATE vs NOW() issue `CREATE TABLE od_bulk ( odsid int(11) NOT NULL AUTO_INCREMENT, Detaild int(11) NOT NULL, InvId int(11) NOT NULL, OrderNo varchar(10) NOT NULL, SerialNumber varchar(32) DEFAULT NULL, InDate date DEFAULT NULL, InTime time DEFAULT NULL, InBy varchar(10) DEFAULT NULL, PRIMARY KEY (odsid), INDEX idxDetaild (Detaild), INDEX idxSerialNumber (SerialNumber) )` – Garik M May 12 '15 at 17:11
  • @GarikM, you'll need to provide more context then, since it is not an actual issue with the query. It could just be a dbForgeStudio bug. That is the application that lets you step through to debug stored routines, right? Also, you could have put the CREATE in the question by editing it. – Uueerdo May 12 '15 at 17:17

0 Answers0