How do i specify a date format while creating a table in mysql?

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.

Whenever we work with databases, we find that almost every single table contains a Date column. After all, the date of the data plays an important role while analyzing it. Storing dates in a specific or understandable format is very important. In this article, we are going to learn how we can specify a Date format on SQL Server.

Let’s create our demo database and table.

Step 1: Create a database

Use the following command to create a database.

Query:

CREATE DATABASE User_details; 

Step 2:Use database

Query:

USE User_details; 

Step 3: Table definition

We have the following GFG_user table in the database.

Query: 

CREATE TABLE GFG_user(Id INT NOT NULL,Dt DATE, 
Address  VARCHAR(100),Dt_FORMATTED AS 
(convert(varchar(255), dt, 104)),   
PRIMARY KEY (Id) );

Output:

Here, we have created a column named Dt_FORMATTED where we are going to save our formatted Date. 

Now, we see the CONVERT() function. The CONVERT() function simply converts a value of any type into a specified datatype.

Syntax:

CONVERT ( data_type ( length ) ,
expression , style )    

By using this function, we are casting the string to a date. In the place of style argument, we have mentioned ‘104’. It is a numeric code to specify the date format.

Check this table to see different codes used for different formats:

Here, We have mentioned only the 10 most used formats. 

Step 4: Insert values

The following command is used to insert values into the table.

Query:

SET DATEFORMAT dmy; INSERT INTO GFG_user
(Id, Dt, Address) VALUES ('1','23.11.2021',
'German');   

In this query, we are usingthe DATEFORMAT setting.

Syntax:

SET DATEFORMAT format    

 When we are inserting the string, the server will try to convert the string to date before inserting it into the table. As it cannot tell if we are putting the month before the date or the date before the month. For example, suppose you are trying to insert 06.07.2000. The server is unable to detect if the date is the 6th of July or it is the 7th of June. Though it uses the localization settings of the user account that is operating to figure that out not mentioning the DATEFORMAT might give you an error as most of the times the account that is running the operation is set to USA format, that is – Month Day Year (mdy).

The error was caused because we wanted to save it as dmy, not mdy. However, using DATEFORMAT will help you to get rid of it.

Output:

We are done with our table, now let’s check if we are getting our desired output or not.

Step 5: View data of the table

Query:

SELECT * FROM GFG_user; 

Output:

We have successfully got our German format Date in the Dt_FORMATTED column.

How do you create a date format in SQL while creating a table?

How do you specify a date format on creating a table and fill it?

How do I format a date in MySQL?

How do I format a date in a table?