IFNULL function

[cms9651]

Hi, I have problem with the IFNULL function:

http://dev.mysql.com/doc/refman/5.0/...unction_ifnull

My code:

IFNULL(NULL, str_to_date(`myDate`, '%d/%m/%Y %H.%i')),

Error:
[Err] 1411 - Incorrect datetime value: 'NULL' for function str_to_date

Can you help me?

[guido2004]

1) Why use IFNULL, if you put NULL as first expression? It means it will always return the second expression.
2) The error is not caused by IFNULL, but by STR_TO_DATE. It seems the value of myDate is NULL.

[PhilipToop]

The documentation says

IFNULL(expr1,expr2)

If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns expr2.

and gives an example

mysql> SELECT IFNULL(NULL,10);
        -> 10

So in your case

IFNULL(NULL, str_to_date(`myDate`, '%d/%m/%Y %H.%i')),

expr2 is always evaluate even mydate is null

You want the function

IF(expr1,expr2,expr3)

If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL) then IF() returns expr2; otherwise it returns expr3.

[cms9651]

2) The error is not caused by IFNULL, but by STR_TO_DATE. It seems the value of myDate is NULL.

Yes, this is the problem... I have records where value of myDate is NULL...
Any suggestion?
Thanks-

[guido2004]

use a CASE , or IF like Philip said.

[cms9651]

use a CASE , or IF like Philip said.

Ok:

IF(str_to_date(`myDate`, '%d/%m/%Y %H.%i'),'0000-00-00','') 

[Err] 1411 - Incorrect datetime value: 'NULL' for function str_to_date

[guido2004]

IF(myDate IS NULL, something, something_else)

[r937]

CASE WHEN myDate IS NULL
     THEN '1999-12-31' -- or whatever date you want
     ELSE STR_TO_DATE(myDate,'%d/%m/%Y %H.%i') END

[cms9651]

IF(myDate IS NULL, something, something_else)

 IF(`myDate` IS NULL, '0000-00-00', str_to_date(`myDate`, '%d/%m/%Y %H.%i')) 

[Err] 1411 - Incorrect datetime value: 'NULL' for function str_to_date

[cms9651]

CASE WHEN myDate IS NULL
     THEN '1999-12-31' -- or whatever date you want
     ELSE STR_TO_DATE(myDate,'%d/%m/%Y %H.%i') END

[Err] 1411 - Incorrect datetime value: 'NULL' for function str_to_date

[cms9651]

CASE WHEN `myDate` IS NULL OR `myDate` = 'NULL'
     THEN '1999-12-31'
     ELSE STR_TO_DATE(myDate,'%d/%m/%Y %H.%i') END

Great, thanks !

[guido2004]

So the value wasn't NULL, but 'NULL' (string value)

[cms9651]

So the value wasn't NULL, but 'NULL' (string value)

Are two values... NULL and 'NULL'...