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

Question

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

User · Accepted Answer

quot MySQL retrieves and displays DATETIME values in  YYYY-MM-DD HH MM SS  format  quot  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

User · Answer

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

User · Answer

try this   DATE NOT NULL FORMAT  YYYY-MM-DD

User · Answer

i have used following line of code  amp  it works fine Thanks      Mithun Sasidharan        SELECT DATE FORMAT column name    d  m  Y   FROM tablename

User · Answer

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

User · Answer

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

User · Answer

Dim x as date   x   dr  appdate   appdate   x tostring  dd MM yyyy     dr is the variable of datareader

User · Answer

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

User · Answer

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

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

Examples related to mysql

Examples related to datetime

Examples related to command-line

Examples related to format

Examples related to create-table