CODEWITHSUNDEEP
phpmysqlsqldatabase
Kuc Ku
Kuc Ku

Reputation: 51

mySQL how to change date format

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.

Upvotes: 2

Views: 4495

Answers (3)

HIRA THAKUR
HIRA THAKUR

Reputation: 17757

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

Upvotes: -1

Kermit
Kermit

Reputation: 34055

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

Upvotes: 4

DevlshOne
DevlshOne

Reputation: 8457

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.

Upvotes: 0

Related Questions