

- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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.
- 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?