Oracle SQL - Default Date Time Constant Format

Introduction

The following table lists the syntax to specify date/time-related constants/literals, using predefined ANSI/ISO SQL standard formats.

Literal
Example
DATE 'yyyy-mm-dd'
DATE '2014-09-25'
TIMESTAMP 'yyyy-mm-dd hh24:mi:ss.ffffff'
[AT TIME ZONE '...']
TIMESTAMP '2014-09-25 23:59:59.99999' AT TIME ZONE 'CET'

TIMESTAMP 'yyyy-mm-dd hh24:mi:ss.ffffff
{+|-}hh:mi'
TIMESTAMP '2014-09-25 23:59:59.99 -5:00'

INTERVAL 'expr' <qualifier>

INTERVAL '1' YEAR
INTERVAL '1 2:3' DAY TO MINUTE

You can experiment with this syntax by entering the following query:

Demo

SQL>
SQL> select DATE '2014-09-25' from dual;

DATE'2014
----------- from  w  w  w .  j  a v  a  2s  .  co m
25-SEP-14

SQL>
SQL> select TIMESTAMP '2014-09-25 23:59:59.99999' AT TIME ZONE 'CET' from dual;

TIMESTAMP'2014-09-2523:59:59.99999'ATTIMEZONE'CET'
---------------------------------------------------------------------------
25-SEP-14 11.59.59.999990000 PM CET

SQL>
SQL> select TIMESTAMP '2014-09-25 23:59:59.99 -5:00' from dual;

TIMESTAMP'2014-09-2523:59:59.99-5:00'
---------------------------------------------------------------------------
25-SEP-14 11.59.59.990000000 PM -05:00

SQL>
SQL> select INTERVAL '1' YEAR from dual;

INTERVAL'1'YEAR
---------------------------------------------------------------------------
+01-00

SQL>
SQL> select INTERVAL '1 2:3' DAY TO MINUTE from dual;

INTERVAL'12:3'DAYTOMINUTE
---------------------------------------------------------------------------
+01 02:03:00

SQL>

Related Topics

Quiz