I have a dateformat but to display on front end I would like to display the data like so.
SELECT STR_TO_DATE('5,2013','%m,%Y');
The result I would like to generate is 'May 2013'.
Add a comment
|
5 Answers
Assuming that you have 5,2013 stored in your database, you need to use DATE_FORMAT after parsing the string:
SELECT DATE_FORMAT(STR_TO_DATE('5,2013','%m,%Y'), '%b %Y')
Why? Because it seems you don't have a date type stored in the database. So you parse it to date using STR_TO_DATE. This give you the default date format. DATE_FORMAT let you apply other formattings on it.
All avaliable formatting values are documented here: https://www.w3schools.com/sql/func_mysql_str_to_date.asp
In general, I would recommend to think about storing date objects instead of custom date strings like 5,2013. This avoids those castings, because you could directly use DATE_FORMAT. It also has benefits on filtering/ordering and performance. Using date types, you could easily sort by it or filter e.g. everything in month X.
I don't say its not possible with custom strings like 5,2013 but would be more complex and could result in bad performance if the db grows.
Comments
Why are you storing dates as string values? Mysql has dedicated data types for date and time values: https://dev.mysql.com/doc/refman/5.7/en/date-and-time-types.html
When using date, you can easily use DATE_FORMAT and set %m,%Y as formatting (second argument).
1 Comment
Zrin
Welcome to SO. It is good to add a working example or a few crucial lines, it makes it much easier to understand your answer. Like:
SELECT DATE_FORMAT(STR_TO_DATE('5,2013','%m,%Y'), '%b %Y')SELECT replace(date_format(str_to_date('5,2013','%m,%Y'),'%M-%Y'),'-',' ');
1 Comment
double-beep
While this code may solve the question, including an explanation of how and why this solves the problem would really help to improve the quality of your post, and probably result in more up-votes. Remember that you are answering the question for readers in the future, not just the person asking now. Please edit your answer to add explanations and give an indication of what limitations and assumptions apply. From Review
As to the format just read the docs: https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format. You need an uppercase M for the month name. And use DATE_FORMAT to get from a date to a string.
SELECT DATE_FORMAT(DATE '2013-05-01', '%M %Y');
Comments
You can use the functions:
str_to_date() to convert the string (after concatenating the prefix '1' to your string) to a valid date and then
date_format() to reformat the date:
SELECT date_format(str_to_date(concat('1,', ?),'%d,%m,%Y'), '%b %Y');
Replace ? with your string.
See the demo.
Result:
May 2013