DATETIME

Syntax:

DATETIME(year, month, day, hour, minute, second)

returns the date with time, expressed as a date-time serial number. year is an integer between 1583 and 9956 or between 0 and 99; month, day, hour, minute,and second are integers.

If month, day, hour, minute and second are not within range for a valid date, the date will 'roll over', as shown below.

Example:

DATETIME(2021, 11, 9, 8, 30, 0)

returns the date 9th November 2021 at 8:30 AM (as a date-time serial number).

DATETIME(2021, 12, 32, 8, 30, 0)

returns 1st January 2022 at 8:30 AM - the date rolls over, as 32nd December 2021 is not valid.

DATETIME(2004, 3, 0, 8, 30, 0)

returns 29th February 2004 at 8:30 AM - the date rolls over backwards, as 0th March 2004 is not valid. 2004 was a leap year.

DATETIME(2021, 15, 8, 8, 30, 0)

returns 8th March 2022 at 8:30 AM - the date rolls over, as there are only 12 months in a year

DATETIME(2021, 10, 8, 28, 30, 0)

returns 9th October 2022 at 4:30 AM - the date rolls over 1 day and the hour is adjusted to 4:30 AM, as the hour is 28 hours.

Application:

Online Order Status Tracking

Let's imagine a scenario for an online order tracking system. When a customer places an order, it goes through several stages, and it's vital to record the exact date and time each stage occurs. This helps with order fulfillment, customer service inquiries, and performance analysis.

Table: Order Status History

Order Status History ID	Order ID	Status	Status Change Timestamp	Notes
101	5001	Pending	Monday, July 28, 2025 at 3:15 AM PDT	Customer placed order online
102	5001	Processing	Monday, July 28, 2025 at 4:00 AM PDT	Order sent to warehouse for picking
103	5002	Pending	Monday, July 28, 2025 at 7:20 AM PDT	Customer placed order online
104	5001	Shipped	Tuesday, July 29, 2025 at 2:30 AM PDT	Package picked up by courier
105	5002	Processing	Tuesday, July 29, 2025 at 3:10 AM PDT	Order sent to warehouse for picking
106	5001	Delivered	Wednesday, July 30, 2025 at 9:00 AM PDT	Package delivered to customer address

Here's a breakdown of how DATETIME is used in the table:

Recording a Specific Point in Time: DATETIME is perfect for capturing the exact moment an event occurs. In this example, the event is a change in an order's status. When an order goes from 'Pending' to 'Processing', the system records not only that the status changed, but also the precise date and time it happened. This is more precise and useful than just recording the date or just the time separately.
Creating a Chronological History: By using DATETIME for each status update, the column creates a chronological log of an order's journey.
Data Integrity and Auditing: Using DATETIME provides an immutable timestamp for each event, which is essential for auditing purposes. If there's a dispute about when an order was shipped or delivered, the Status Change Timestamp provides a reliable record. It helps ensure data integrity by preventing users or systems from manually entering incorrect or inconsistent time data. The system populates this field with the current date and time at the moment the event is recorded, minimizing human error.