Datetime expressions

A datetime expression generates a value of the datetime data type.

The following syntax is provided:

{TIMESTAMP | DATE} string

You can combine TIMESTAMP or DATE and a string literal to generate a value of the TIMESTAMP or DATE type. The string format must be consistent with that of the system variables NLS_TIMESTAMP_FORMAT and NLS_DATE_FORMAT.

You can query the values of the system variables by executing the following SQL statement:

select * from v$nls_parameters where parameter like '%FORMAT';

For example, if the value of the system variable NLS_TIMESTAMP_FORMAT is YYYY-MM-DD HH24:MI:SS.FF, you can use the following expression to generate a value of the TIMESTAMP type.

select timestamp '2020-01-01 10:00:00' from dual;
+-------------------------------+
| TIMESTAMP'2020-01-0110:00:00' |
+-------------------------------+
| 2020-01-01 10:00:00.000000000 |
+-------------------------------+