ICT 0417 - Databases

18. Displaying Date and Time Data

This section focuses on how to control the format in which date and time data is displayed within a database. Different database systems offer various formatting options, allowing you to present data in a user-friendly and consistent manner.

Understanding Date and Time Data Types

Databases typically store date and time information using specific data types. Common types include:

• Date: Stores only the date (e.g., 2023-10-27).
• Time: Stores only the time (e.g., 14:30:00).
• DateTime: Stores both date and time (e.g., 2023-10-27 14:30:00).

Formatting Options

The available formatting options depend on the database management system (DBMS) being used. Common formats include:

• YYYY-MM-DD: Year-Month-Day (e.g., 2023-10-27)
• MM/DD/YYYY: Month/Day/Year (e.g., 10/27/2023)
• DD-MMM-YYYY: Day-Month-Year (e.g., 27-Oct-2023)
• HH:MM:SS: Hours:Minutes:Seconds (e.g., 14:30:00)
• HH:MM AM/PM: Hours:Minutes AM/PM (e.g., 02:30 PM)

Database System Specific Examples

Here are examples of how to format date and time data in some popular DBMS:

MySQL

MySQL uses the DATE_FORMAT() function.

SQL	Output
SELECT DATE_FORMAT(your_date_column, '%d/%m/%Y') AS formatted_date FROM your_table;	Example: 27/10/2023
SELECT DATE_FORMAT(your_datetime_column, '%H:%i:%s %p') AS formatted_time FROM your_table;	Example: 02:30:00 PM

SQL Server

SQL Server uses the CONVERT() function with style codes.

SQL	Output
SELECT CONVERT(VARCHAR, your_date_column, 103) AS formatted_date FROM your_table;	Example: 10/27/2023
SELECT CONVERT(VARCHAR, your_datetime_column, 108) AS formatted_time FROM your_table;	Example: 2:30:00 PM

PostgreSQL

PostgreSQL uses the TO_CHAR() function.

SQL	Output
SELECT TO_CHAR(your_date_column, 'DD/MM/YYYY') AS formatted_date FROM your_table;	Example: 27/10/2023
SELECT TO_CHAR(your_datetime_column, 'HH24:MI:SS') AS formatted_time FROM your_table;	Example: 14:30:00

Importance of Consistent Formatting

Maintaining consistent date and time formatting is crucial for:

• Data readability: Easier to understand and interpret data.
• Data validation: Ensuring data is entered in the correct format.
• Reporting: Presenting data in a standardized way across reports.
• Data exchange: Facilitating data sharing between different systems.

Practice

Practice using the appropriate formatting functions for the specific DBMS you are working with. Experiment with different format codes to achieve the desired output.