Recently I worked on Mysql and need to work on date and time formats and faced one issue where I need to convert from long date format(e.g Fir Dec 12 00:00:00 IST 2014 ) to date format yyyy-mm-dd format. So for doing this, we need to parse the long date format for that I used concat, str_to_date and substring inbuilt Mysql methods.
substring in Mysql: Substring function used to get the substring from given string. For that we need to specify the ranges of the substring. Below is the example
Syntax:
substring(input string,substringStartingPos[,lenghtOfTheString])
Example:
select substring('Hello substring',4) gives 'lo substring' as it starts from 4th position to end of the string
select substring('Hello substring',4,4) gives 'lo s' as it starts from the 4th position and it takes 4 characters as length specified 4
select substring('Hello substring',-4) gives 'ring' as we specified the range four characters rom back(minus specifies from back).
Similarly I have taken the Day, Month and year from the long date pattern format as below.
select substring('Fir Dec 12 00:00:00 IST 2014',-4) gives '2014' which is a year
select substring('Fir Dec 12 00:00:00 IST 2014',4,4) gives 'Dec' which is a month
select substring('Fir Dec 12 00:00:00 IST 2014',9,2) gives '12' which is a day
Concat in Mysql: Concat function will combine more than one string into one string. Below is the syntax and example.
Syntax:
concat(str1,str2,str3,....)
Example:
select concat('Hello',' ', 'World!!') gives 'Hello World!!' as we passed three string to concat function.
Concat string function used to separate the strings with special characters as delimiters.
Coming to our problem of converting string to date format now as we know concat and substring functions. For that we need to use str_to_date inbuilt Mysql function.
str_to_date in Mysql: str_to_date function as name says, it converts string to date format. Its inverse of date_format() function which converts from date to string.
Syntax:
str_to_date(string,dateformat)
Example:
select str_to_date('Dec 12,2014', '%M %e,%Y') gives '2014-12-12' as %M is for month, %e as day and %Y is for Year. There are many formats for the date, we can check here for more.
And finally the resulted query for converting from long date format to yyyy-mm-dd is below.
select STR_TO_DATE(concat(substring('Fir Dec 12 00:00:00 IST 2014',4,7),',',substring('Fir Dec 12 00:00:00 IST 2014',-4)), '%M %e,%Y') as date
Which will give the date as '2014-12-12' which is required format.
Happy Coding!!