IFNULL function

Databases
1

Hi, I have problem with the IFNULL function:

http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#function_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?

2
  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.
3

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.
4

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

5

use a CASE , or IF like Philip said.

6

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

:shifty:

7

IF(myDate IS NULL, something, something_else)

8 
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
9 
 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

:sick:

10

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

11 
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 ! :smiley:

12

So the value wasn’t NULL, but ‘NULL’ (string value) :smiley:

13

Are two values… NULL and ‘NULL’…