MySQL-এ DATE ও TIME কলাম কম্বাইন করে DATETIME তৈরি

CONCAT() দিয়ে DATE + TIME = DATETIME

আলাদা DATE এবং TIME কলাম থেকে DATETIME তৈরি করতে CONCAT(date, ' ', time) ব্যবহার করুন।

সিনট্যাক্স

UPDATE yourTable SET datetime_col = CONCAT(date_col, ' ', time_col);

উদাহরণ

টেবিল তৈরি

mysql> create table getDateTimeFieldsDemo (
    -> ShippingDate date,
    -> ShippingTime time,
    -> Shippingdatetime datetime
    -> );
Query OK, 0 rows affected (0.50 sec)

ডেটা ইনসার্ট (DATETIME NULL)

mysql> insert into getDateTimeFieldsDemo(ShippingDate,ShippingTime) values('2018-01-21','09:45:34');
Query OK, 1 row affected (0.16 sec)
mysql> insert into getDateTimeFieldsDemo(ShippingDate,ShippingTime) values('2013-07-26','13:21:20');
Query OK, 1 row affected (0.13 sec)
mysql> insert into getDateTimeFieldsDemo(ShippingDate,ShippingTime) values('2017-12-31','15:31:40');
Query OK, 1 row affected (0.17 sec)
mysql> insert into getDateTimeFieldsDemo(ShippingDate,ShippingTime) values('2019-03-07','12:13:34');
Query OK, 1 row affected (0.41 sec)

আপডেট করার আগে

mysql> SELECT * FROM getDateTimeFieldsDemo;

আউটপুট

+--------------+--------------+------------------+
| ShippingDate | ShippingTime | Shippingdatetime |
+--------------+--------------+------------------+
| 2018-01-21   | 09:45:34     | NULL             |
| 2013-07-26   | 13:21:20     | NULL             |
| 2017-12-31   | 15:31:40     | NULL             |
| 2019-03-07   | 12:13:34     | NULL             |
+--------------+--------------+------------------+

CONCAT দিয়ে আপডেট

mysql> UPDATE getDateTimeFieldsDemo SET Shippingdatetime = CONCAT(ShippingDate, ' ', ShippingTime);
Query OK, 4 rows affected (0.09 sec)

আপডেটের পর

mysql> SELECT * FROM getDateTimeFieldsDemo;

আউটপুট

+--------------+--------------+---------------------+
| ShippingDate | ShippingTime | Shippingdatetime    |
+--------------+--------------+---------------------+
| 2018-01-21   | 09:45:34     | 2018-01-21 09:45:34 |
| 2013-07-26   | 13:21:20     | 2013-07-26 13:21:20 |
| 2017-12-31   | 15:31:40     | 2017-12-31 15:31:40 |
| 2019-03-07   | 12:13:34     | 2019-03-07 12:13:34 |
+--------------+--------------+---------------------+

বিকল্প: TIMESTAMP(date_col, time_col) ফাংশনও ব্যবহার করা যায়।