MySQL - Date Column Format

Question

I am familiar with DATE_FORMAT function which can display the the record from my date field in the format specified. However, I would like to create a table with a date field that only accepts my format.

Here's what I have done so far:

CREATE TABLE test_table (
id INT AUTO_INCREMENT, 
f_name VARCHAR(40) NOT NULL, 
l_name VARCHAR(25) NOT NULL, 
date_hired DATE NOT NULL
);

Inserting a record with a date_hired value of '2013-03-01' will be inserted as '1/03/2013 12:00:00 AM' which is far from my expected result (I would like the format the way it was inserted). Any feedback? Did I miss something?

Thanks,
Michael

Format it in your query using DATE_FORMAT. The date type itself doesn't have any type and is stored numerically — zerkms
– zerkms, Commented Jun 18, 2013 at 7:36
Hi @zerkms, I'm new with MySQL. What do you mean by 'stored numerically'? — Michael 'Maik' Ardan
– Michael 'Maik' Ardan, Commented Jun 18, 2013 at 7:41
ignore it an read the whole comment. And check dev.mysql.com/doc/refman/5.5/en/… additionally — zerkms
– zerkms, Commented Jun 18, 2013 at 8:32
Date is saved in numeric format not only in MySQL but in most of databases. Generally any date is difference of time with a predefinded date from history. So lets say there is db with 1/1/1900 as their predifined historic date as base. And now if u want to save 1/1/2013, it will internally calculate difference of days from historic date to your date and would convert it in millisec (or ticks) and will save the final number in db. Reverse process would be done when u want read date field from db. Just before displaying the result to the user it shows format based on db settings — Nitin Agrawal
– Nitin Agrawal, Commented Jun 18, 2013 at 8:44

Amir · Accepted Answer · 2019-05-03 13:02:35Z

11

You can't change the format during table create, you can change the format of date for displaying user by using you programming logic like if you are using PHP as your server side language the you can convert it your desired format.

edited May 3, 2019 at 13:02

Amir

8,9397 gold badges47 silver badges52 bronze badges

answered Jun 18, 2013 at 7:45

kundan

1261 silver badge2 bronze badges

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

1 Comment

crashwap Over a year ago

I would have upvoted this correct answer if it had a couple of examples, and a reference to PHP date formats.

Collectives™ on Stack Overflow

MySQL - Date Column Format

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related