Date Types

Table 1 Date types

Name	Description	Storage Space	Value Range
DATETIME/DATE	Stores date type data without time zone. 1. Stores year, month, day, hour, minute, and second.	8 bytes	[0001-01-01 00:00:00, 9999-12-31 23:59:59]
TIMESTAMP[(n)]	Stores timestamp type data without time zone. 1. Stores year, month, day, hour, minute, second, and microsecond. 2. The valid range for `n` is [0,6], indicating the precision of the fractional seconds. `TIMESTAMP(n)` can also be written without the parameter, i.e., as `TIMESTAMP`, in which case the precision of the fractional seconds defaults to 6.	8 bytes	[0001-01-01 00:00:00.000000, 9999-12-31 23:59:59.999999]
TIMESTAMP[(n)] WITH TIME ZONE	Stores timestamp type data with time zone. 1. Stores year, month, day, hour, minute, second, microsecond, and time zone. 2. The valid range for `n` is [0,6], indicating the precision of the fractional seconds. `TIMESTAMP(n)` can also be written without the parameter, i.e., as `TIMESTAMP`, in which case the precision of the fractional seconds defaults to 6.	12 bytes	[0001-01-01 00:00:00.000000, 9999-12-31 23:59:59.999999]
TIMESTAMP[(n)] WITH LOCAL TIME ZONE	Stores timestamp type data with time zone. The time zone is not stored; data is converted to the database time zone's `TIMESTAMP` when stored, and converted to the current session's time zone `TIMESTAMP` when viewed by users. 1. Stores year, month, day, hour, minute, second, and microsecond. 2. The valid range for `n` is [0,6], indicating the precision of the fractional seconds. `TIMESTAMP(n)` can also be written without the parameter, i.e., as `TIMESTAMP`, in which case the precision of the fractional seconds defaults to 6.	8 bytes	[0001-01-01 00:00:00.000000, 9999-12-31 23:59:59.999999]

Table 2 shows the template that can be used to format date and time values.

Table 2 Patterns for date/time formatting

Category	Pattern	Description
Hour	HH	Hour of the day (01-12)
	HH12	Hour of the day (01-12)
	HH24	Hour of the day (00-23)
	TZH	Time zone hour
Minute	MI	Minute (00-59)
Minute	TZM	Time zone minute
Second	FF	Microsecond (000000-999999)
	FF3	Microsecond (000-999)
	FF6	Microsecond (000000-999999)
	SS	Second (00-59)
	SSSSS	Seconds past midnight (0-86399)
A.M./P.M.	AM or A.M.	Morning indicator
A.M./P.M.	PM or P.M.	Afternoon indicator
Year	YYYY	Year (4 digits)
	YYY	Last three digits of the year
	YY	Last two digits of the year
	Y	Last digit of the year
Month	MONTH	Full uppercase month name
	MON	Abbreviated uppercase month name
	MM	Month number (01-12)
Day	DAY	Full uppercase day name
	DY	Abbreviated uppercase day name
	DDD	Day of the year (001-366)
	DD	Day of the month (01-31)
	D	Day of the week (1-7; 1 for Sunday)
Week	W	Week of the month (1–5, week 1 starts on the first day of the month)
Week	WW	Week of the year (1–53, week 1 starts on the first day of the year)
Century	CC	Century (2 digits)
Quarter	Q	Quarter of the year

Example:

SQL> select to_char(systimestamp, 'YYYY-MM-DD HH:MI:SS A.M.');

TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH:MI:SS A.M.')
-------------------------------------------------
2025-11-12 09:37:31 AM

1 rows fetched.

SQL> select to_char(systimestamp, 'YYYY-MM-DD HH:MI:SSXFF4');

TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH:MI:SSXFF4')
------------------------------------------------
2025-11-12 09:27:23.6485

1 rows fetched.

-- Create a table.
SQL> CREATE TABLE date_t1 (a datetime,b timestamp(6), c timestamp(4) with time zone, d TIMESTAMP(2) WITH LOCAL TIME ZONE); 

-- Insert data.
SQL> INSERT INTO date_t1 VALUES ('2025-11-12 09:37:31', systimestamp, systimestamp, systimestamp);

-- View the data.
SQL> SELECT * FROM date_t1;

A                      B                                C                                        D
---------------------- -------------------------------- ---------------------------------------- --------------------------------
2025-11-12 09:37:31    2025-11-20 17:35:31.740367       2025-11-20 17:35:31.7404 +08:00          2025-11-20 17:35:31.74

1 rows fetched.

SQL> DROP TABLE date_t1;

Date Types ​

Date Types