[mysql] 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

This question is related to mysql datetime command-line format create-table

The answer is


"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

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).


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


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.


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

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

**


try this:

DATE NOT NULL FORMAT 'YYYY-MM-DD'

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.


Dim x as date 

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

dr is the variable of datareader


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


Examples related to mysql

Implement specialization in ER diagram How to post query parameters with Axios? PHP with MySQL 8.0+ error: The server requested authentication method unknown to the client Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver' phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' is not supported How to resolve Unable to load authentication plugin 'caching_sha2_password' issue Connection Java-MySql : Public Key Retrieval is not allowed How to grant all privileges to root user in MySQL 8.0 MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client

Examples related to datetime

Comparing two joda DateTime instances How to format DateTime in Flutter , How to get current time in flutter? How do I convert 2018-04-10T04:00:00.000Z string to DateTime? How to get current local date and time in Kotlin Converting unix time into date-time via excel Convert python datetime to timestamp in milliseconds SQL Server date format yyyymmdd Laravel Carbon subtract days from current date Check if date is a valid one Why is ZoneOffset.UTC != ZoneId.of("UTC")?

Examples related to command-line

Git is not working after macOS Update (xcrun: error: invalid active developer path (/Library/Developer/CommandLineTools) Flutter command not found Angular - ng: command not found how to run python files in windows command prompt? How to run .NET Core console app from the command line Copy Paste in Bash on Ubuntu on Windows How to find which version of TensorFlow is installed in my system? How to install JQ on Mac by command-line? Python not working in the command line of git bash Run function in script from command line (Node JS)

Examples related to format

Brackets.io: Is there a way to auto indent / format <html> Oracle SQL - DATE greater than statement What does this format means T00:00:00.000Z? How to format date in angularjs How do I change data-type of pandas data frame to string with a defined format? How to pad a string to a fixed length with spaces in Python? How to format current time using a yyyyMMddHHmmss format? java.util.Date format SSSSSS: if not microseconds what are the last 3 digits? Formatting a double to two decimal places How enable auto-format code for Intellij IDEA?

Examples related to create-table

Field 'id' doesn't have a default value? MySQL: Error Code: 1118 Row size too large (> 8126). Changing some columns to TEXT or BLOB Create hive table using "as select" or "like" and also specify delimiter #1064 -You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version C# - Create SQL Server table programmatically How do I create a table based on another table Creating new table with SELECT INTO in SQL create table in postgreSQL PostgreSQL Error: Relation already exists Mysql: Setup the format of DATETIME to 'DD-MM-YYYY HH:MM:SS' when creating a table