Be able to set display format of date/time data

Resources | Subject Notes | Information Communication Technology ICT

ICT 0417 - Databases - Date/Time Display Format

Databases - Setting Display Format of Date/Time Data

Objective

Be able to set the display format of date and time data within a database system.

Understanding Date and Time Data Types

Databases use specific data types to store date and time information. Common data 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 the date and the time (e.g., 2023-10-27 14:30:00).

Why is Display Format Important?

The way date and time data is displayed can vary depending on the database system, operating system, and user preferences. It's crucial to control the display format for:

  • User-friendliness: Presenting data in a familiar and easily understandable format.
  • Data consistency: Ensuring that date and time values are displayed uniformly across the application.
  • Data validation: Enforcing specific date and time formats during data entry.

Setting Display Formats (Example - SQL)

Many database systems provide functions or settings to control the display format of date and time values. Here's an example using SQL (Structured Query Language), which is commonly used with relational databases like MySQL, PostgreSQL, and SQL Server:

  1. %Y: Four-digit year (e.g., 2023)
  2. %m: Two-digit month (e.g., 01, 12)
  3. %d: Two-digit day of the month (e.g., 01, 31)
  4. %H: Two-digit hour (24-hour format) (e.g., 00, 23)
  5. %i: Two-digit minute (e.g., 00, 59)
  6. %s: Second (e.g., 00, 59)
  7. %T: Time in 24-hour format (e.g., 14:30:45)
  8. %D: Date in mm/dd/yyyy format (e.g., 10/27/2023)
  9. %c: Date and time representation appropriate for the current locale.
Function/Keyword Description Example
DATE_FORMAT() This function allows you to format date and time values according to a specified format string. 
                        
                            SELECT DATE_FORMAT(order_date, '%d/%m/%Y') AS formatted_date
                            FROM orders;

Output: The order_date will be displayed in the format dd/mm/yyyy.
STRFTIME() (SQLite) In SQLite, STRFTIME() is used for formatting date and time values. 
                        
                            SELECT STRFTIME('%Y-%m-%d %H:%M:%S', now());

Output: The current date and time will be displayed in the format YYYY-MM-DD HH:MM:SS.
Format Masks Format masks are strings that specify the desired output format. Common format masks include:

Example Scenario

Consider a database table called orders with a order_date column (data type: DateTime). To display the order date in the format dd-mm-yyyy, you would use the DATE_FORMAT() function (or equivalent in your database system) as shown in the example above.

Important Considerations

  • The specific functions and format masks available may vary depending on the database system you are using.
  • Ensure that the format mask you use is compatible with the data type of the date/time column.
  • Consider the user's locale (language and regional settings) when choosing a display format.