CODEWITHSUNDEEP
mysql
Callum Jones
Callum Jones

Reputation: 55

MySQL date format to string

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'.

Upvotes: 0

Views: 3447

Answers (5)

forpas
forpas

Reputation: 164224

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

Upvotes: 0

kernel
kernel

Reputation: 11

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).

Upvotes: 1

Lion
Lion

Reputation: 17928

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.

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 95101

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');

Upvotes: 0

P.Salmon
P.Salmon

Reputation: 17665

SELECT replace(date_format(str_to_date('5,2013','%m,%Y'),'%M-%Y'),'-',' ');

Upvotes: 0

Related Questions