how to change date format in mysql

Question

The default date format in my SQL is : '2019-05-06' and its fine but when i insert date to my table i want this format 2019-5-6 not the above format

It means month and day must be start 1 to 30 not 01 to 31.Is there any way to change default format in my sql?

i use a library to convert timestamp to persian date and after the convert i have this 1397/5/6 and when insert to table it becomes this 1397/05/06 — sajadsholi
– sajadsholi, Commented Jan 6, 2019 at 10:57
Where does come from your date_time format. Is it come from or get data from your system? — A.A Noman
– A.A Noman, Commented Jan 6, 2019 at 11:10

GMB · Accepted Answer · 2019-01-06 13:05:43Z

You seem to be looking for the MySQL STR_TO_DATE function :

It takes a string str and a format string format. STR_TO_DATE() returns a DATETIME value if the format string contains both date and time parts, or a DATE or TIME value if the string contains only date or time parts.

So if the date coming out of your application is like '2019-5-6', to convert it to a MySQL date you need :

STR_TO_DATE('2019-5-6', '%Y-%c-%e')

In an INSERT statement :

INSERT INTO mytable VALUES(1, STR_TO_DATE('2019-5-6', '%Y-%c-%e'));

Tip :

%Y : Year as a numeric, 4-digit value
%c : numeric month name (0 to 12)
%e: day of the month as a numeric value (0 to 31)

Dezso Gabos · Accepted Answer · 2019-01-06 10:54:56Z

The default way to store a date in a MySQL database is by using DATE. The proper format of a DATE is: YYYY-MM-DD. If you try to enter a date in a format other than the Year-Month-Day format, it might work but it won't be storing the dates as you expect.

In order to run a MySQL Insert command and add the current date into your table you can use MySQL's built-in function CURDATE() in your query.

An example of how to Insert a Date in MySQL using CURDATE

$query_auto = "INSERT INTO tablename (col_name, col_date) VALUE ('DATE: Auto CURDATE()', CURDATE() )";

Also, you can run a query to set the date manually

An example of how to Insert a Date in MySQL manually

$query_manual = "INSERT INTO tablename (col_name, col_date) VALUES ('DATE: Manual Date', '2008-7-04')";

It is recommended to do the date formatting when doing a query, like so:

SELECT DATE_FORMAT(BirthDate, "%W %M %e %Y") FROM Employees;

You can find more examples of formatting the date here: https://www.w3schools.com/sql/func_mysql_date_format.asp

Collectives™ on Stack Overflow

how to change date format in mysql

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related