Changing date format using FORMAT

Question

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?

Apparently it can be used for dates: technet.microsoft.com/en-us/library/hh213505.aspx ..... I tried SELECT DATE_FORMAT (date, 'yyyy/mm/dd' ) AS t FROM tracker; but it just returns NULL for all — dlofrodloh
– dlofrodloh, Commented Mar 10, 2015 at 17:59
@flodorhlod, the link you posted is specific to Microsoft SQL Server, but the question is tagged MySQL. Both are similar products, but differ slightly in their SQL syntax and functionality. — Haleemur Ali
– Haleemur Ali, Commented Mar 10, 2015 at 18:02
please also post the result you get from the queries SELECT date FROM tracker LIMIT 10 & DESCRIBE tracker. — Haleemur Ali
– Haleemur Ali, Commented Mar 10, 2015 at 18:05

John Conde · Accepted Answer · 2015-03-10 18:11:08Z

3

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;

edited Mar 10, 2015 at 18:11

answered Mar 10, 2015 at 18:00

John Conde

220k100 gold badges464 silver badges504 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Haleemur Ali · Accepted Answer · 2015-03-10 18:00:01Z

1

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.

answered Mar 10, 2015 at 18:00

Haleemur Ali

28.6k6 gold badges67 silver badges89 bronze badges

2 Comments

John Conde Over a year ago

Does this work if the date is not in a standard format? Isn't 'dd/mm/yyyy' ambiguous?

Haleemur Ali Over a year ago

you're right, the varchar has to be converted to a date first, then rendering can happen via the date_format function.

Collectives™ on Stack Overflow

Changing date format using FORMAT

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related