String to Date format by str_to_date()

STR_TO_DATE('Dec/15/2008', '%M/%d/%Y');  => 2008-12-15
SELECT STR_TO_DATE('31/Jan/2008', '%d/%M/%Y');   => 2008-01-31
SELECT STR_TO_DATE( '12/01/2023', '%d/%m/%Y' ); => 2023-01-12
SELECT STR_TO_DATE( '31/Jan/2008 23:52', '%d/%M/%Y %H:%i' ) => 2008-01-31 23:52:00
SELECT STR_TO_DATE( '31/01/2023 23:52', '%d/%m/%Y %H:%i' ); => 2023-01-31 23:52:00
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
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 format is not supported by the string data given then the output will be NULL. For any illegal date or time string value also the output will be NULL.

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
STR_TO_DATE( '32/Apr/2008', '%d/%M/%Y' ) => NULL
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
Subscribe to our YouTube Channel here


* indicates required
Subscribe to plus2net


    Thanks a lot, this has been very helpful


    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 .

    Post your comments , suggestion , error , requirements etc here

    SQL Video Tutorials

    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2024 All rights reserved worldwide Privacy Policy Disclaimer