Convert MySQL Date Format from yyyy-mm-ddT00:00:00.000Z to yyyy-dd-mm for Node.js Applications

---

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: mysql returns the date in this format "yyyy-mm-ddT00:00:00.000Z" I want it in "yyyy-dd-mm" format

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---

Understanding the Problem

When you insert a date into your MySQL database, you usually format it as YYYY-MM-DD. However, upon retrieval, MySQL presents it in an ISO 8601 format such as yyyy-mm-ddT00:00:00.000Z. If you're interested in only the date portion (YYYY-MM-DD), utilizing this format directly may seem unnecessary or excessive.

Example Query and Output

Here’s an example of how a query might look:

[[See Video to Reveal this Text or Code Snippet]]

With the output being:

[[See Video to Reveal this Text or Code Snippet]]

The date format returned is not what we want.

Solutions to the Format Issue

1. Using DATE_FORMAT

If you aren't concerned about the timezone and just need the date format, you can modify your SQL query to include the DATE_FORMAT function. This will return the date in the desired format directly from the database.

Example SQL Query:

[[See Video to Reveal this Text or Code Snippet]]

2. Manipulating the Date in JavaScript

If you'd like to process the date after retrieval, you can achieve this using JavaScript by modifying the data returned from the database. Here’s how to do it:

[[See Video to Reveal this Text or Code Snippet]]

With this approach, you are transforming the date into the YYYY-MM-DD format after the data is fetched.

3. Handling Timezones

If you need to consider the browser's timezone and display the correct date accordingly, you'll have to do additional formatting in your frontend code.

Here's a sample function to manage date formatting in the browser:

[[See Video to Reveal this Text or Code Snippet]]

This function allows you to format the date while considering the local timezone settings, ensuring the date reflects what the user expects based on their local configuration.

Conclusion