How to Insert mm/dd/yyyy Format Dates in MySQL?

In MySQL, the “DATE” is one of the most commonly used data types that is used for managing the date values. By default, MySQL uses the “yyyy/mm/dd” format for saving a date value and it cannot be changed. However, users prefer to save the date in the “mm/dd/yyyy” format. To do so, MySQL provides different functions, such as the “DATE_FORMAT(‘date’, ‘format’)” function. It can format the date in user required format.

This blog will provide the method of inserting dates in mm/dd/yyyy format using different functions in MySQL.

How to Insert mm/dd/yyyy Format Dates in MySQL?

To insert the mm/dd/yyyy format dates in MySQL, try out the below-provided instructions:

• Access the MySQL server and list the databases.
• Change the database and execute the “CREATE TABLE <table-name> (col1, col2,..); command to create a new table.
• Run the “INSERT INTO <table-name> (col1, col2,..) VALUES(val1, (STR_TO_DATE(‘string’, ‘date-format’)));” command to insert the date in mm/dd/yyyy format.
• Use the “SELECT DATE_FORMAT(<string>, ‘<date-format>’) FROM <table-name>;” command to view the date in the desired format.

Step 1: Connect Command Prompt With MySQL

First, execute the “mysql” command to access the MySQL server by providing the username and password:

Step 2: View Databases

Now, run the “SHOW” command to list the databases:

From the displayed database list, we have selected the “mynewdb” database:

Step 3: Change Database

To change the database, run the “USE” command:

Step 4: Create New Table

Next, create a new table in the current database by utilizing the “CREATE” statement along with the “TABLE” option, table name, column names, and their data types:

Here:

• “CREATE” statement is used for generating a table in the database.
• “TABLE” is the default option in MySQL.
• “demo_test” is the table name that we are creating.

According to the following output, the “Query OK” shows that the query has been executed successfully:

Step 5: Insert Data in Table

Then, run the “INSERT” statement with the “INTO” keyword to add the required data in the table fields using the “STR_TO_DATE()” function:

Here:

• “Name” and “Date_of_birth” are the table column names.
• “VALUES” statement that returns a set of one or multiple rows as a table.
• “STR_TO_DATE()” function is used to return a date based on a provided string and desired format as parameters.
• “’%m-%d-%Y’” is the desired date format:

Step 6: Use DATE_FORMAT() Function

Use the “DATE_FORMAT()” function with the required parameters to show the date in “mm/dd/yyyy” format:

Here:

• “SELECT” statement is used to select the data from the MySQL database.
• “DATE_FORMAT()” function is used to format a date as specified, such as in the “’%m/%d/%Y’”.
• “FROM” clause is used to extract the selected records from a database table.
• “demo_test” is our database table name.

It can be seen in the provided output; we have successfully retrieved the date in “mm/dd/yyyy” format:

That’s all! We have compiled the method to insert and retrieve the dates in mm/dd/yyyy format in MySQL.

Conclusion

To insert the mm/dd/yyyy format dates in MySQL, execute the “CREATE TABLE <table-name> (col1, col2,..); command to create a new table. After that, run the “INSERT INTO <table-name> (col1, col2,..) VALUES(val1, (STR_TO_DATE(‘string’, ‘date-format’)));” command to insert the date in mm/dd/yyyy format. Use the “SELECT DATE_FORMAT(<date>, <date-format>) FROM <table-name>;” command to view the date in the desired format. This blog illustrated the method of inserting mm/dd/yyyy format dates in MySQL.