How many digits should be there in string or number so that it can be specified as a date value by MySQL?

While considering the year as 4-digit value, minimum of 8 digits in a string or number is required for MySQL to specify it as a date value. In this case, if we also want to store microseconds then the value can be up to a maximum of 20 digits.

mysql> Select TIMESTAMP('20171022040536.100000');
+-----------------------------------+
| TIMESTAMP('20171022040536100000') |
+-----------------------------------+
| 2017-10-22 04:05:36.100000        |
+-----------------------------------+
1 row in set, 1 warning (0.00 sec)

The query above is taking 20 digits string for TIMESTAMP value. Last 6 digits are for microseconds.

mysql> Select TIMESTAMP(20171022);
+---------------------+
| TIMESTAMP(20171022) |
+---------------------+
| 2017-10-22 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

The query above is taking 8 digits string for TIMESTAMP value.

mysql> Select TIMESTAMP(201710);
+-------------------+
| TIMESTAMP(201710) |
+-------------------+
| NULL              |
+-------------------+
1 row in set, 1 warning (0.00 sec)

The query above returns NULL because the numbers of a digit are less than 8.

In contrast, while considering the year as 2-digit value, minimum of 6 digits in a string or number is required for MySQL to specify it as a date value. In this case, if we also want to store microseconds then the value can be up to a maximum of 18 digits.

mysql> Select TIMESTAMP(171022);
+---------------------+
| TIMESTAMP(171022)   |
+---------------------+
| 2017-10-22 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

The query above is taking 6 digits string for TIMESTAMP value.

mysql> Select TIMESTAMP('171022040536.200000');
+----------------------------------+
| TIMESTAMP('171022040536.200000') |
+----------------------------------+
| 2017-10-22 04:05:36.200000       |
+----------------------------------+
1 row in set (0.00 sec)

The query above is taking 18 digits string for TIMESTAMP value. Last 6 digits after dot (.) are for microseconds.

mysql> Select TIMESTAMP(1710);
+-----------------+
| TIMESTAMP(1710) |
+-----------------+
| NULL            |
+-----------------+
1 row in set, 1 warning (0.00 sec)

The query above returns NULL because the numbers of digit are less than 6.

Sreemaha

Updated on 29-Jan-2020 06:43:57

• Related Questions & Answers
• How can Tensorflow be used to export the model so that it can be used later?
• How can it be possible to invert a string in MySQL?
• How many ways a String object can be created in java?
• Write a palindrome program in JavaScript so that only alphanumeric values should be allowed?
• How can we decide that custom exception should be checked or unchecked in java?
• Is it necessary that a try block should be followed by a catch block in Java?
• Why the sound in cinema halls is kept so high? Can it be dangerous for many individuals?
• Find maximum number that can be formed using digits of a given number in C++
• Fix MySQL ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
• Maximum number of edges to be added to a tree so that it stays a Bipartite graph in C++
• What kind of string comparison, case-sensitive or not, can be performed by MySQL?
• How can Unicode string be split, and byte offset be specified with Tensorflow & Python?
• How to set that the specified element/group of elements should be disabled in HTML?
• Find the largest number that can be formed with the given digits in C++
• How can we search a record from MySQL table having a date as a value in it?