CODEWITHSUNDEEP
mysqlsqldatedate-format
dlofrodloh
dlofrodloh

Reputation: 1744

Changing date format using FORMAT

I've got dates in my database stored as varchars in the format: dd/mm/yyyy e.g. 1/3/2015

I'm trying to change the format to yyyy/mm/dd so I can convert them into timestamps.

When I try this:

SELECT FORMAT (date, 'dd/mm/yyyy', 'yyyy/mm/dd' ) AS t FROM tracker;

It returns only one number per record which happens to be the day. So if I run the query on the following dates: 2/3/2015 and 6/2/2014 then the query returns 2 and 6.

How can I get it to return the correct format?

Upvotes: 0

Views: 114

Answers (2)

John Conde
John Conde

Reputation: 219804

Looks like you're using the wrong MySQL functions here. You're looking for STR_TO_DATE() which will convert that date string into a date value which MySQL can work with. You can then use DATE_FORMAT() to convert it to a new date string of your choosing.

SELECT DATE_FORMAT(STR_TO_DATE(date, '%d/%m/%Y'), '%Y/%m/%d') AS t FROM tracker;

Upvotes: 3

Haleemur Ali
Haleemur Ali

Reputation: 28233

To output datetime & timestamps in specific formats, you can use the DATE_FORMAT function.

It works like this: 

SELECT DATE_FORMAT(my_date_field, '%Y-%m-%d %H:%M') FROM my_table

Incidentally, specifying the format as '%Y/%m/%d' will render your date field as 1999/12/31.

To output the datetime value in a timestamp use the function UNIX_TIMESTAMP.

Upvotes: 1

Related Questions