mySQL how to change date format

Question

the date format in mySQL have type (yyyy-mm-dd), now I try to reverse it (dd-mm-yyyy). I got table user

CREATE TABLE USER ( USER_ID INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY, DOB DATE, );

so when i insert value into that:

INSERT INTO USER(DOB) VALUES(DATE_FORMAT('13-07-1990','%d,%m,%Y));

however, it does not work. It notice that I should use 1990-07-13 instead of '13-07-1990'. please help me to do that. Thanks.

This format is accepted as standard. It goes like yyyy-mm-dd-hh-ii-ss. As you can see starts with years-months-days-hours-minutes-seconds, which is really easy to remember. — php_nub_qq
– php_nub_qq, Commented Nov 17, 2013 at 18:35
Is not possible to change the internal date format of mysql. — Babblo
– Babblo, Commented Nov 17, 2013 at 18:35
You cannot control internal representation of MySql date type. You can , however, format your query output any way you want. — PM 77-1
– PM 77-1, Commented Nov 17, 2013 at 18:36

Kermit · Accepted Answer · 2013-11-17 18:40:48Z

The ANSI SQL standard format for date is YYYY-MM-DD HH:MM:SS. It's easier to capture the date in this format for insertion into a database.

In order to store the date with the format you're using (MM-DD-YYYY) you need to use the STR_TO_DATE function which allows you to convert a string to a date. The syntax for this function is:

STR_TO_DATE(str, format)

The specifiers for the format can be found here.

For your INSERT statement, you would use:

INSERT INTO `user` (`dob`) VALUES ( STR_TO_DATE('13-07-1990','%d-%m-%Y') )

HI, when I used the query above, it appeared like this July, 13 1990 00:00:00+0000, I used %m instead of %M, why does it built like this? Please help me, thanks

DevlshOne · Accepted Answer · 2013-11-17 18:38:23Z

DATE_FORMAT is only used to save dates into varchar fields with non-standard formats or to retrieve them from the DB in a non-standard format. You may NOT save something in a DATE type field as any format besides YYYY-MM-DD.

HIRA THAKUR · Accepted Answer · 2013-11-17 19:45:38Z

FROM THE DOCS : The DATE type is used for values with a date part but no time part. MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format. The supported range is '1000-01-01' to '9999-12-31'.

My Way would be a little different.I would compute my date in php and pass the desired format to the query rather than computing it in query.

$your_date= "2010-03-21"; $desired_format = date("d-m-Y", strtotime($your_date));//returns 21-03-2010 $desired_format = date("m-d-Y", strtotime($your_date));//returns 03-21-2010

by using strtotime and date function,You can convert your date in to any format you desire

You can't assume that the OP is using server-side scripting.
My mistake. I would still have to argue that the date conversion on the application side is not only inefficient, the database functions are specifically designed to handle conversions.
i am googling about which is more efficient and less time consuming..let us see

Collectives™ on Stack Overflow

mySQL how to change date format

3 Answers 3

4 Comments

Comments

5 Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

5 Comments

Related