Sunday, July 10, 2011

String to Date format by str_to_date()

Posted by Kaleeswaran at 1:51 PM

String to Date format by str_to_date()

Converting string data to date format by using str_to_date() function to insert to mysql table 

Storing string data in mysql table date field by converting to date format by str_to_date function 

We know we have to use particular format for date & time field before storing to mysql date field. So if a user entered data is to be stored in a table then we have to change the format by using different string functions and convert them to acceptable date format. You can read this tutorial to know how the string functions are used to format the user entered data to mysql table. 

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 

STR_TO_DATE('Dec/15/2008', '%M/%d/%Y'); => 2008-12-15
STR_TO_DATE('31/Jan/2008', '%d/%M/%Y'); => 2008-01-31
STR_TO_DATE( '31/Jan/2008 23:52', '%d/%M/%Y %H:%i' ) => 2008-01-31 23:52:00

Try with some illegal date

STR_TO_DATE( '32/Apr/2008', '%d/%M/%Y' ) => NULL


--
Thank you.

http://www.plus2net.com/sql_tutorial/str_to_date.php



No comments:

Post a Comment

Subscribe to: Post Comments (Atom)