Oracle SQL - Default Date Time Constant Format

  1. HOME
  2. Oracle SQL
  3. Select
  4. 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
-----------  w  w w . j  av  a 2 s .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