
- Learn MySQL
- MySQL - Home
- MySQL - Introduction
- MySQL - Installation
- MySQL - Administration
- MySQL - PHP Syntax
- MySQL - Connection
- MySQL - Create Database
- MySQL - Drop Database
- MySQL - Select Database
- MySQL - Data Types
- MySQL - Create Tables
- MySQL - Drop Tables
- MySQL - Insert Query
- MySQL - Select Query
- MySQL - Where Clause
- MySQL - Update Query
- MySQL - Delete Query
- MySQL - Like Clause
- MySQL - Sorting Results
- MySQL - Using Join
- MySQL - NULL Values
- MySQL - Regexps
- MySQL - Transactions
- MySQL - Alter Command
- MySQL - Indexes
- MySQL - Temporary Tables
- MySQL - Clone Tables
- MySQL - Database Info
- MySQL - Using Sequences
- MySQL - Handling Duplicates
- MySQL - SQL Injection
- MySQL - Database Export
- MySQL - Database Import
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 Articles
- A number is increased by 20% of its value. By what percentage should the number now be decreased so that it gets its original value?
- How many numbers can be formed by arranging the digits of the number 211 ?
- How can it be possible to invert a string in MySQL?
- By what number should $5^{-1}$ be multiplied so that the product may be equal to $(-7)^{-1}$?
- By what number should $(-15)^{-1}$ be divided so that the quotient may be equal to $(-5)^{-1}$?
- How can Tensorflow be used to export the model so that it can be used later?
- By what number should $(\frac{1}{2})^{-1}$ multiplied so that the product many be equal to $(\frac{-4}{7})^{-1}$?
- How can MySQL interpret the number and string, having no delimiter, as a date?
- By what number should $(\frac{5}{3})^{-2}$ be multiplied so that the product may be $(\frac{7}{3})^{-1}$?
- By what number should we multiply $\frac{-8}{13}$ so that the product may be $24 $?
- Find maximum number that can be formed using digits of a given number in C++
- How can we search a record from MySQL table having a date as a value in it?
- How many ways a String object can be created in java?
- Which least number should be subtracted from 1000, so that the difference is exactly divisible by 35.
- How can we decide that custom exception should be checked or unchecked in java?
