IF() দিয়ে NULL হ্যান্ডলিং
DATE_FORMAT(NULL, ...) কল করলে NULL রিটার্ন করে, কিন্তু এক্সপ্রিসিট IF() ব্যবহার করে নিশ্চিত করা যায় যে NULL ইনপুটের জন্য NULL আউটপুট আসে।
সিনট্যাক্স
SELECT IF(yourDateColumn, DATE_FORMAT(yourDateColumn, '%d/%m/%Y'), NULL) FROM yourTable;
উদাহরণ
টেবিল তৈরি (DATETIME nullable)
mysql> create table returnNullWhenInputIsNullDemo (
-> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> ShippingDate datetime
-> );
Query OK, 0 rows affected (1.21 sec)
ডেটা ইনসার্ট (NULL সহ)
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)
সকল রেকর্ড
mysql> SELECT * FROM returnNullWhenInputIsNullDemo;
আউটপুট
+----+---------------------+ | 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 | +----+---------------------+
IF() দিয়ে DATE_FORMAT
mysql> SELECT IF(ShippingDate, DATE_FORMAT(ShippingDate, '%d/%m/%Y'), NULL) FROM returnNullWhenInputIsNullDemo;
আউটপুট
+-------------------------------------------------------------+ | 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 | +-------------------------------------------------------------+
ব্যাখ্যা: NULL ShippingDate-র জন্য আউটপুট NULL। নন-NULL ডেটার জন্য 'dd/mm/YYYY' ফরম্যাটে রিটার্ন। COALESCE(DATE_FORMAT(...), NULL) ও কাজ করবে।
