CODEWITHSUNDEEP
mysqldatetimecommand-lineformatcreate-table
Iam Zesh
Iam Zesh

Reputation: 1847

Mysql: Setup the format of DATETIME to 'DD-MM-YYYY HH:MM:SS' when creating a table

After googling around, I cannot find a way to create a new table with a DATETIME column with the default format set to 'DD-MM-YYYY HH:MM:SS'

I saw a tutorial in which it was done in phpmyadmin so I suspect that I could use mysql via command line and achieve the same thing when creating my new table with 

CREATE TABLE ()

Thank you in advance

Upvotes: 61

Views: 501335

Answers (9)

Mirko Akov
Mirko Akov

Reputation: 2447

"MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format." This is from mysql site. You can store only this type, but you can use one of the many time format functions to change it, when you need to display it.

Mysql Time and Date functions

For example, one of those functions is the DATE_FORMAT, which can be used like so:

SELECT DATE_FORMAT(column_name, '%m/%d/%Y %H:%i') FROM tablename

Upvotes: 97

Datta Salunkhe
Datta Salunkhe

Reputation: 85

i have used following line of code & it works fine Thanks.... @Mithun Sasidharan **

SELECT DATE_FORMAT(column_name, '%d/%m/%Y') FROM tablename

**

Upvotes: 5

aksu
aksu

Reputation: 5235

As others have explained that it is not possible, but here's alternative solution, it requires a little tuning, but it works like datetime column.

I started to think, how I could make formatting possible. I got an idea. What about making trigger for it? I mean, adding column with type char, and then updating that column using a MySQL trigger. And that worked! I made some research related to triggers, and finally come up with these queries:

CREATE TRIGGER timestampper BEFORE INSERT ON table
FOR EACH
ROW SET NEW.timestamp = DATE_FORMAT(NOW(), '%d-%m-%Y %H:%i:%s');

CREATE TRIGGER timestampper BEFORE UPDATE ON table
FOR EACH
ROW SET NEW.timestamp = DATE_FORMAT(NOW(), '%d-%m-%Y %H:%i:%s');

You can't use TIMESTAMP or DATETIME as a column type, because these have their own format, and they update automatically.

So, here's your alternative timestamp or datetime alternative! Hope this helped, at least I'm glad that I got this working.

Upvotes: 9

Gigoland
Gigoland

Reputation: 1453

try this:

DATE NOT NULL FORMAT 'YYYY-MM-DD'

Upvotes: 0

ynot
ynot

Reputation: 21

Dim x as date 

x = dr("appdate")
appdate = x.tostring("dd/MM/yyyy")

dr is the variable of datareader

Upvotes: 2

colithium
colithium

Reputation: 10327

I'm pretty certain that you can't change the datetime format in mysql. The phpmyadmin setting is probably applying a custom format as it reads the datetime (using DATE_FORMAT or something from php). It shouldn't matter what format the database uses, format in the application to display it as you wish.

Date formatting is a pretty common task. I typically like to abstract it out into internationalization code or, if you don't need to deal with i18n, into a common date utility library. It helps keep things consistent and makes it easier to change later (or add i18n support).

Upvotes: 4

Devart
Devart

Reputation: 121932

This cannot be done for the table; besides, you even cannot change this default value at all.

The answer is a server variable datetime_format, it is unused.

Upvotes: 2

Rahul
Rahul

Reputation: 77876

No you can't; datetime will be stored in default format only while creating table and then you can change the display format in you select query the way you want using the Mysql Date Time Functions

Upvotes: 2

Mithun Sasidharan
Mithun Sasidharan

Reputation: 20940

Use DATE_FORMAT function to change the format. 

SELECT DATE_FORMAT(CURDATE(), '%d/%m/%Y')

SELECT DATE_FORMAT(column_name, '%d/%m/%Y') FROM tablename

Refer DOC for more details

Upvotes: 20

Related Questions