- Data Structure
- Networking
- RDBMS
- Operating System
- Java
- MS Excel
- iOS
- HTML
- CSS
- Android
- Python
- C Programming
- C++
- C#
- MongoDB
- MySQL
- Javascript
- PHP
- Physics
- Chemistry
- Biology
- Mathematics
- English
- Economics
- Psychology
- Social Studies
- Fashion Studies
- Legal Studies
- 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 can MySQL interpret the number and string, having no delimiter, as a date?
If a string or number, even without any delimiter, in the format of YYYYMMDDHHMMSS or YYMMDDHHMMSS is making sense as the date is provided then MySQL interpret that string as a valid date.
Examples are given for valid as well as invalid dates −
mysql> Select Timestamp(20171022040536); +---------------------------+ | Timestamp(20171022040536) | +---------------------------+ | 2017-10-22 04:05:36 | +---------------------------+ 1 row in set (0.00 sec) mysql> Select Timestamp('20171022040536'); +-----------------------------+ | Timestamp('20171022040536') | +-----------------------------+ | 2017-10-22 04:05:36 | +-----------------------------+ 1 row in set (0.00 sec) mysql> Select Timestamp('171022040536'); +---------------------------+ | Timestamp('171022040536') | +---------------------------+ | 2017-10-22 04:05:36 | +---------------------------+ 1 row in set (0.00 sec)
Above queries shows the examples of valid string as well number which can be interpreted as date values by MySQL.
mysql> Select Timestamp('20171022048536'); +-----------------------------+ | Timestamp('20171022048536') | +-----------------------------+ | NULL | +-----------------------------+ 1 row in set, 1 warning (0.00 sec)
Above query is the example for the invalid string. MySQL returns NULL because the string is having wrong value (85) for a minute.
mysql> Select Timestamp('20171322040536'); +-----------------------------+ | Timestamp('20171322040536') | +-----------------------------+ | NULL | +-----------------------------+ 1 row in set, 1 warning (0.00 sec)
In above query, MySQL returns NULL because the string is having wrong value (13) for a month.
Advertisements