What would be output if we will try to extract time values by providing the date values only to MySQL EXTRACT() function?

MySQLMySQLi Database

When we try to extract hour value from a date, then EXTRACT() function will give the output 0 with a warning as shown in the below-given example −

mysql> Select EXTRACT(Hour from '2017-10-20');

+---------------------------------+
| EXTRACT(Hour from '2017-10-20') |
+---------------------------------+
| 0                               |
+---------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> Show Warnings;

+---------+------+----------------------------------------------+
| Level   | Code | Message                                      |
+---------+------+----------------------------------------------+
| Warning | 1292 | Truncated incorrect time value: '2017-10-20' |
+---------+------+----------------------------------------------+
1 row in set (0.00 sec)

Now, when we try to extract minute value from a date, then EXTRACT() function will give the value of century from date as the output with a warning as shown in the below-given example −

mysql> Select EXTRACT(Minute from '2017-10-20');
+-----------------------------------+
| EXTRACT(Minute from '2017-10-20') |
+-----------------------------------+
| 20                                |
+-----------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> Show Warnings;
+---------+------+----------------------------------------------+
| Level   | Code | Message                                      |
+---------+------+----------------------------------------------+
| Warning | 1292 | Truncated incorrect time value: '2017-10-20' |
+---------+------+----------------------------------------------+
1 row in set (0.00 sec)

Now, when we try to extract seconds value from a date, then EXTRACT() function will give the value of year as the output with a warning as shown in the below given example −

mysql> Select Extract(Second from '2017-10-20');

+-----------------------------------+
| Extract(Second from '2017-10-20') |
+-----------------------------------+
| 17                                |
+-----------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> Show Warnings;

+---------+------+----------------------------------------------+
| Level   | Code | Message                                      |
+---------+------+----------------------------------------------+
| Warning | 1292 | Truncated incorrect time value: '2017-10-20' |
+---------+------+----------------------------------------------+
1 row in set (0.00 sec)

We will get similar results on trying to get the time values(Hour, Minute & Seconds) from current date i.e. by using Curdate() at the place of date.

raja
Published on 19-Feb-2018 10:20:15
Advertisements