Be able to set display format of date/time data

Cambridge IGCSE ICT (0417) – Topic 18: Databases

Learning Objective

Students will be able to set the display format of date and time data in relational database management systems (RDBMS) and understand how this skill fits into the complete database development cycle required by the IGCSE ICT syllabus.

1. How Date/Time Formatting Fits into the Whole Database Cycle

The IGCSE ICT syllabus (Section 18) expects candidates to demonstrate knowledge and practical skills across seven sub‑topics. The table below shows each sub‑topic, the key skills involved, and the specific relevance of date‑/time formatting.

2. Date and Time Data Types (What the Exam Expects)

• DATE     Stores a calendar date (year‑month‑day).
• TIME     Stores a time of day (hour‑minute‑second).
• DATETIME  Stores both date and time together (no automatic time‑zone handling).
• TIMESTAMP Stores a point in time, usually in UTC; many systems can auto‑convert to the session’s time‑zone.
• INTERVAL  (advanced – not required for IGCSE; used for time spans such as “5 days”).

Choosing the correct type matters because it determines the range of values, the functions you can use, and how the system deals with time‑zones.

3. Formatting Functions by Database System

3.1 Common Format Specifiers

Note: MySQL uses the percent sign (%) for all specifiers, whereas PostgreSQL/Oracle use plain letters, and SQL Server follows .NET date‑format patterns.

4. Setting the Default Display Format

1. Identify the RDBMS you are using (MySQL, PostgreSQL, Oracle, SQL Server).
2. Choose the appropriate function from the table in Section 3.
3. Apply the function in a SELECT statement whenever you need a formatted value for a query, report or export.
4. For front‑end tools (Access, LibreOffice Base, web apps) you can set the format in the form/report property sheet instead of using a SQL function.
5. Set a session‑wide default only when required:

   • SQL Server: SET DATEFORMAT dmy; (affects only date literals, not the output of FORMAT).
   • MySQL: SET lctimenames = 'en_GB'; changes month name language but not numeric format.
   • PostgreSQL: SET datestyle = 'ISO, DMY'; changes how DATE literals are interpreted.
   • Oracle: No single command; you must use ALTER SESSION SET NLSDATEFORMAT = 'DD/MM/YYYY';.

   Use these only when every query in the session should share the same format.

Example – Formatting in a Query (MySQL)

SELECT
order_id,
DATEFORMAT(orderdate, '%d/%m/%Y')   AS orderdateformatted,
DATEFORMAT(deliverytime, '%H:%i')   AS deliverytimeformatted
FROM orders
WHERE order_date >= '2024-01-01'
ORDER BY order_date DESC;

Example – Formatting in a Report (Microsoft Access)

1. Open the report in Design View.
2. Select the text box bound to the date field.
3. In the Property Sheet, set Format to dd/mm/yyyy.
4. Save and switch to Print Preview – the dates now appear in the required format.

5. Time‑Zone Conversion (When Working with TIMESTAMP)

Most RDBMS store timestamps in UTC. Convert to the required local zone before applying the formatting function.

After conversion, wrap the result in the appropriate formatting function, e.g.:

-- PostgreSQL example
SELECT TO_CHAR(
ts_column AT TIME ZONE 'UTC' AT TIME ZONE 'Europe/London',
'DD-Mon-YYYY HH24:MI'
) AS local_ts
FROM events;

6. Practical Skills Checklist (What You Must Be Able to Do in the Exam)

• Design a simple relational schema (tables, fields, primary/foreign keys).
• Create a data‑entry form and set a custom date format for a field.
• Write a SELECT query that uses a formatting function to display dates as required.
• Produce a report (or print preview) where the date column follows the chosen format.
• Convert a UTC timestamp to a local time‑zone and then format it for display.
• Explain why a particular format was chosen (user expectations, international standards, or exam instructions).

7. Practice Questions

1. MySQL – Current date
   

   Write a query that returns today’s date in the format YYYY‑MM‑DD.

   SELECT DATE_FORMAT(CURDATE(), '%Y-%m-%d') AS today;

2. PostgreSQL – Detailed timestamp
   

   Table events has column event_ts of type TIMESTAMP. Show how to display it as Month DD, YYYY HH24:MI (e.g., “April 23, 2024 14:30”).
   

   SELECT TOCHAR(eventts, 'Month DD, YYYY HH24:MI') AS formatted_ts
   FROM events;

3. Comparison of functions
   

   Explain the difference between DATEFORMAT (MySQL) and TOCHAR (PostgreSQL/Oracle) in terms of syntax, supported specifiers and typical use‑cases.

   • DATE_FORMAT(date, '%d/%m/%Y') – MySQL uses a leading % for every specifier; format string is a single quoted literal.
   • TO_CHAR(date, 'DD/MM/YYYY') – PostgreSQL/Oracle use plain letters; no %. They also support additional date‑time elements (e.g., DY, FM for fill‑mode).
   • Use DATEFORMAT only in MySQL; use TOCHAR in PostgreSQL and Oracle. Both return a VARCHAR value that can be displayed or exported.

4. Oracle – UTC to BST
   

   Convert a UTC TIMESTAMP column called log_time to British Summer Time and format it as DD‑MMM‑YYYY HH24:MI.
   

   SELECT TO_CHAR(
   FROMTZ(CAST(logtime AS TIMESTAMP), 'UTC')
   AT TIME ZONE 'Europe/London',
   'DD-MON-YYYY HH24:MI'
   ) AS bst_log
   FROM audit_log;

5. Design task (Paper 2)
   

   Sketch a simple relational database for a school library (tables: Books, Members, Loans). Indicate the data type you would use for the loan_date field and state the display format you would apply in the loan‑record form.
   

   • loan_date – data type DATE.
   • Form display format – set to dd/mm/yyyy (British style) in the form’s property sheet.

6. Form validation (Paper 3)
   

   In a data‑entry form, you need to ensure the user cannot enter a future date for dateofbirth. Describe how you would set this validation rule and how the date would be shown to the user.
   

   • Validation rule (e.g., in Access): Is Null Or [dateofbirth] <= Date().
   • Set the field’s Format property to dd/mm/yyyy so the user sees the same layout as the validation message.
   • If the rule fails, display an alert: “Date of birth cannot be later than today.”

8. Key Takeaways

• Use the RDBMS‑specific formatting function (DATEFORMAT, TOCHAR, FORMAT) to control the appearance of dates and times in queries, forms and reports.
• Remember that format specifiers differ between systems; always refer to the table in Section 3.1.
• Convert UTC timestamps to the required time‑zone first, then apply the formatting function – otherwise the displayed time may be incorrect.
• Consistent date/time formatting improves readability, avoids misinterpretation, and is a required part of the IGCSE ICT exam tasks for queries, forms and reports.
• Effective database work also includes designing tables, creating forms, validating data, writing queries, and producing reports – date/time formatting is just one essential piece of the whole process.