
- 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
What MySQL returns if specified format string is not as per accordance with the date string passed as arguments to STR_TO_DATE() function?
If the specified format string and date string did not match then MySQL will return NULL value as output along with a warning. Following is an example to understand the same −
mysql> Select STR_TO_DATE('20172810', '%Y,%d%m'); +------------------------------------+ | STR_TO_DATE('20172810', '%Y,%d%m') | +------------------------------------+ | NULL | +------------------------------------+ 1 row in set, 1 warning (0.00 sec)
The query above returns NULL as output because the format string is having a comma (,) after %Y but date string is not having any comma after 2017.
mysql> Show Warnings\G *************************** 1. row *************************** Level: Warning Code: 1411 Message: Incorrect datetime value: '20172810' for function str_to_date 1 row in set (0.00 sec)
Similarly, on distinguishing the order of date units in format string from the date string, MySQL will perform same as above. An example is given below to understand it −
mysql> Select STR_TO_DATE('20172810', '%d%m%Y'); +-----------------------------------+ | STR_TO_DATE('20172810', '%d%m%Y') | +-----------------------------------+ | NULL | +-----------------------------------+ 1 row in set, 1 warning (0.00 sec)
In the above query, the order of units in the format string is changed from the order of units in the date string.
mysql> Show Warnings\G *************************** 1. row *************************** Level: Warning Code: 1411 Message: Incorrect datetime value: '20172810' for function str_to_date 1 row in set (0.00 sec)
- Related Articles
- How to get string as date in MySQL with dates as dot format specifier?
- What MySQL returns if we include time components along with date component as an argument to DATEDIFF() function?
- What MySQL returns if we include date components along with time component as an argument to TIMEDIFF() function?
- What happens if the value of number ‘N’ in CONV() function is not as per accordance with its base?
- Select timestamp as date string in MySQL?
- Which MySQL function returns a specified number of characters of a string as output?
- What MySQL returns if the search string is not in the list of strings provided as argument in FIELD() function?
- How to format a string to date in as dd-MM-yyyy using java?
- What MySQL returns on passing an invalid string as an argument to STR_TO_DATE() function?
- What MySQL returns if we use NULL, as both the arguments, as one of the argument and as a separator, in CONCAT_WS() function?
- How to format date string in PowerShell?
- Java Program to parse string date value with default format
- Set format specifier in MySQL STR_TO_DATE() and convert string to date
- MySQL ORDER BY Date field not in date format?
- How to format a date to String in Java?

Advertisements