Menu
I'm doing something like this:
SELECT date_format(mydate, '%d/%m/%Y') FROM xyz;
When mydate is NULL, date_format returns 00/00/0000. This is correct, but how can I make it so that it returns NULL when the input is NULL?
546
We can return 0 for NULL in MySQL with the help of IFNULL() method. The syntax of IFNULL() is as follows. IFNULL(YOUREXPRESSION,0);
To look for NULL values, you must use the IS NULL test. The following statements show how to find the NULL phone number and the empty phone number: mysql> SELECT * FROM my_table WHERE phone IS NULL; mysql> SELECT * FROM my_table WHERE phone = ''; See Section 3.3.
In MySQL, NULL is considered as a 'missing, unknown value', as opposed to no value. Take a look at this MySQL Reference on NULL. Any arithmetic comparison with NULL does not return true or false, but returns NULL instead., So, NULL != 'C' returns NULL , as opposed to returning true.
The CASE statement cannot have an ELSE NULL clause, and it is terminated with END CASE instead of END . For the first syntax, case_value is an expression. This value is compared to the when_value expression in each WHEN clause until one of them is equal.
SELECT IF(mydate,date_format(mydate, '%d/%m/%Y'),NULL) FROM xyz;
Source: http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html
78
You can wrap this into a IF-Clause, like this:
SELECT IF(mydate,DATE_FORMAT(mydate, '%d/%m/%Y'),NULL) FROM xyz;
That said, if your variable mydate is not a date value, the query in your post should return (NULL) anyway.
21
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
