How to change (format) date output in mysql?

Question

I am trying to format the date output in mySql to something like 01-DEC-1977, and I am ready to throw my computer because I just can't get it to work. this is my table:

CREATE TABLE myAuthor ( ID int(05) NOT NULL, Full_Name varchar(50) NOT NULL, Birthdate date, Country varchar(50) );

This is the data I am inserting:

INSERT INTO myAuthor(ID, Full_Name, Birthdate, Country) values(1001, 'Bob Writer', 19770112, 'USA');

and this is what I am attempting thus far to do to format the date:

select 'ID', 'Full_Name', DATE_FORMAT('Birthdate','%d-%b-%Y') from myauthor;

When I do that the data comes out as null. this is the only thing I have managed to do that doesn't spit out an error or truncate the date all together.

Is there a way to format the date so that once entered it just comes out as DD-MON_YYYY, aand if not, how can I do this, or even better, what am I doing wrong? I have tried switching the date entered into insert where it is formatted with day, month and year, but I get an error when trying that. Any help would be greatly appreciated.

winmutt · Accepted Answer · 2014-11-03 04:11:38Z

1

You have single quotes and not back ticks around your column names, try:

SELECT ID, Full_name,DATE_FORMAT(Birthdate, '%d-%b-%Y') from myauthor;

answered Nov 3, 2014 at 4:11

winmutt

4052 silver badges7 bronze badges

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

2 Comments

Xanthian23 Over a year ago

Ug, now I feel like and idiot. I haven't used mySql in a while, been using Base to play around with SQL and it has me using the single quotes for columns, so i naturally just did that. Thank you sooooo much. I thought I was going crazy.

winmutt Over a year ago

No worries, everytime I put something down and come back months later, something is either broken or of poor design.

Collectives™ on Stack Overflow

How to change (format) date output in mysql?

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related