Be able to set display format of date/time data

📚 18 Databases – Setting Display Format of Date/Time Data

Why Do We Care About Date/Time Formats?

Dates and times are like the “addresses” of events in a database. If the address is written in a confusing way, it’s hard to find the right event. Just as you would want your birthday written as DD/MM/YYYY so you know which day it is, databases need a clear format so that queries, reports and users can read them easily. 🎯

Common Date/Time Formats

  • ISO 8601 – YYYY-MM-DD HH:MM:SS (e.g., 2024-04-26 14:30:00). This is the standard format for storing data.
  • US Format – MM/DD/YYYY (e.g., 04/26/2024).
  • UK Format – DD/MM/YYYY (e.g., 26/04/2024).
  • Full Text – 26 April 2024 or April 26, 2024.

How to Change the Display Format in SQL

Most database systems let you use a FORMAT() or TO_CHAR() function to change how a date appears when you retrieve it. Below are examples for MySQL, SQL Server and PostgreSQL.

Database SQL Statement Result Example
MySQL SELECT DATE_FORMAT(order_date, '%d-%m-%Y') AS formatted_date FROM orders; 26-04-2024
SQL Server SELECT FORMAT(order_date, 'dd/MM/yyyy') AS formatted_date FROM orders; 26/04/2024
PostgreSQL SELECT TO_CHAR(order_date, 'DD/MM/YYYY') AS formatted_date FROM orders; 26/04/2024

Step‑by‑Step: Changing a Date Format in MySQL

  1. Open your MySQL client (e.g., phpMyAdmin, MySQL Workbench).
  2. Run a SELECT query on the table that contains the date column.
  3. Use DATE_FORMAT() to specify the new format.
  4. Check the output – it should now show the date in the chosen style.
  5. Optional: Create a VIEW that always shows the formatted date for easier reuse.

Practical Example – Student Attendance

Imagine a school database that records when students check in. The raw data might be stored as 2024-04-26 08:15:00. For a report, we want to show it as 26/04/2024 08:15 AM so teachers can read it quickly.

Raw Data Formatted (MySQL)
2024-04-26 08:15:00 DATE_FORMAT(check_in, '%d/%m/%Y %h:%i %p')

Result: 26/04/2024 08:15 AM – now it reads like a normal clock time. ⏰

Quick Quiz

  • What function would you use in PostgreSQL to display a date as April 26, 2024?
  • In SQL Server, how would you show the time in 24‑hour format?
  • Why is it better to store dates in ISO 8601 format internally?

Key Takeaways

  • Use FORMAT(), DATE_FORMAT() or TO_CHAR() to change how dates appear.
  • Keep the stored format consistent (ISO 8601) and format only when displaying.
  • Choose a format that matches the audience – students, teachers, or international partners.
  • Test your queries to ensure the output matches expectations.

Revision

Log in to practice.

3 views 0 suggestions