Return null for date_format when input is null in MySQL?

Use IF() function to return null for date_format when input is null in MySQL. The syntax is as follows −

SELECT IF(yourDateColumnName,date_format(yourDateColumnName, '%d/%m/%Y'),NULL) FROM yourTableName;

To understand the above syntax, let us create a table. The query to create a table is as follows −

mysql> create table returnNullWhenInputIsNullDemo    -> (    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> ShippingDate datetime    -> ); Query OK, 0 rows affected (1.21 sec)

Now you can insert some records in the table using insert command. The query is as follows −

mysql> insert into returnNullWhenInputIsNullDemo(ShippingDate) values('2019-01-21'); Query OK, 1 row affected (0.37 sec) mysql> insert into returnNullWhenInputIsNullDemo(ShippingDate) values(NULL); Query OK, 1 row affected (0.16 sec) mysql> insert into returnNullWhenInputIsNullDemo(ShippingDate) values('2018-03-25'); Query OK, 1 row affected (0.17 sec) mysql> insert into returnNullWhenInputIsNullDemo(ShippingDate) values(NULL); Query OK, 1 row affected (0.17 sec) mysql> insert into returnNullWhenInputIsNullDemo(ShippingDate) values(NULL); Query OK, 1 row affected (0.10 sec) mysql> insert into returnNullWhenInputIsNullDemo(ShippingDate) values('2012-04-23'); Query OK, 1 row affected (0.20 sec) mysql> insert into returnNullWhenInputIsNullDemo(ShippingDate) values('2018-12-01'); Query OK, 1 row affected (0.23 sec) mysql> insert into returnNullWhenInputIsNullDemo(ShippingDate) values('2016-11-13'); Query OK, 1 row affected (0.13 sec)

Display all records from the table using select statement. The query is as follows −

mysql> select *from returnNullWhenInputIsNullDemo;

The following is the output −

+----+---------------------+ | Id | ShippingDate | +----+---------------------+ | 1 | 2019-01-21 00:00:00 | | 2 | NULL | | 3 | 2018-03-25 00:00:00 | | 4 | NULL | | 5 | NULL | | 6 | 2012-04-23 00:00:00 | | 7 | 2018-12-01 00:00:00 | | 8 | 2016-11-13 00:00:00 | +----+---------------------+ 8 rows in set (0.00 sec)

Here is the query to return null for date_format when input is null in MySQL −

mysql> SELECT IF(ShippingDate,date_format(ShippingDate, '%d/%m/%Y'),NULL) FROM returnNullWhenInputIsNullDemo;

The following is the output −

+-------------------------------------------------------------+ | IF(ShippingDate,date_format(ShippingDate, '%d/%m/%Y'),NULL) | +-------------------------------------------------------------+ | 21/01/2019                                                  | | NULL                                                        | | 25/03/2018                                                  | | NULL                                                        | | NULL                                                        | | 23/04/2012                                                | | 01/12/2018 | | 13/11/2016 | +-------------------------------------------------------------+ 8 rows in set (0.05 sec)