How to format a date with SQL

Problem

You have a date in your SQL database but it's not in the format you want. You need to change the format of the date.

Sample Data

Let's assume we have a table named Orders with the following data:

MySQL Solution

SELECT OrderID, Product, DATE_FORMAT(OrderDate, '%d-%m-%Y') as FormattedDate
FROM Orders;

Explanation (MySQL)

In MySQL, the DATE_FORMAT() function is used to format the date. The format specifiers for the date are %d (day), %m (month), and %Y (year). In the above query, we are changing the format of the OrderDate to 'day-month-year'.

PostgreSQL Solution

SELECT OrderID, Product, TO_CHAR(OrderDate, 'DD-MM-YYYY') as FormattedDate
FROM Orders;

Explanation (PostgreSQL)

In PostgreSQL, the TO_CHAR() function is used to format the date. Similar to MySQL, the format specifiers for the date are 'DD' (day), 'MM' (month), and 'YYYY' (year). In the above query, we are changing the format of the OrderDate to 'day-month-year'.