Sử dụng hàm IF() để trả về NULL cho DATE_FORMAT khi đầu vào là NULL trong MySQL. Cú pháp như sau:
SELECT IF(tenCotNgay, DATE_FORMAT(tenCotNgay, '%d/%m/%Y'), NULL) FROM tenBangCuaBan;
Để hiểu cú pháp, hãy tạo một bảng:
mysql> CREATE TABLE returnNullWhenInputIsNullDemo ( -> Id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, -> ShippingDate DATETIME -> ); Query OK, 0 rows affected (1.21 sec)
Chèn bản ghi:
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)
Hiển thị tất cả bản ghi:
mysql> SELECT * FROM returnNullWhenInputIsNullDemo;
Kết quả:
+----+---------------------+ | 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)
Truy vấn trả về NULL cho DATE_FORMAT khi đầu vào NULL:
mysql> SELECT IF(ShippingDate, DATE_FORMAT(ShippingDate, '%d/%m/%Y'), NULL) FROM returnNullWhenInputIsNullDemo;
Kết quả:
+-------------------------------------------------------------+ | 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)