Converting string to date format by using str_to_date() function to store in records of MySQL table

SELECT STR_TO_DATE('Dec/15/2008', '%M/%d/%Y');
SELECT STR_TO_DATE('31/Jan/2008', '%d/%M/%Y');
SELECT STR_TO_DATE('12/01/2023', '%d/%m/%Y');
SELECT STR_TO_DATE('31/01/2023 23:52', '%d/%m/%Y %H:%i');

We have to use particular format for date & time data before storing to MySQL date field. String to Date MySQL function

For a date field it should be

YYYY-mm-dd

Example: 2005-12-26

For a date and time field it should be

YYYY-mm-dd H:i:s

Example: 2005-12-26 23:50:30

You can read this tutorial to know how the string functions are used to format the user entered data to MySQL table.

INSERTING PHP Current Date and time to MySQL table using YYYY-mm-dd format and using STR_TO_DATE()

By using the function str_to_date() we can convert the string data to MySQL required format of date field. This function requires the string data and the format in which it is available.

STR_TO_DATE(string_data, format)

If the input cannot be parsed using the supplied format, STR_TO_DATE() returns NULL and MySQL can produce a warning. Modern MySQL also performs stricter range checking for invalid dates, so applications should validate user input before storing it.

Now let us try with some examples of this str_to_date function and its outputs.

SELECT STR_TO_DATE('12/15/2008', '%m/%d/%Y');

Output of above query is 2008-12-15

Same way here are some examples and the outputs of str_to_date function for your understanding Try with some illegal date

SELECT STR_TO_DATE('not-a-date', '%Y-%m-%d');

Output: NULL (with a warning). Learn how str_to_date is used to format date value stored as text in Varchar field of MySQL table
SQL Date References Storing date in MySQL date field

All Date & Time Functions DATE_FORMAT() Insert date/time

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author Watch on YouTube

Connect:

Subscribe to our YouTube Channel here

dhamu	14-07-2013
hi. i have two radio button. one button for show login datetime and another button for logout datetime to show. when i click login button it should store login datetime then after that if i click logout button it should store logout time. i want to store login & logout datetime in mysql using php. please help me .

vicky	01-07-2012
Thanks a lot, this has been very helpful

String to Date format by str_to_date()

Subhendu Mohapatra