OracleBrains.Com header image 2

ANSI date literal in Oracle

December 6th, 2006 by Rajender Singh · No Comments

Lets first create a table as follows:

SQL> CREATE TABLE datetest(
2 datecol DATE);

Table created.

In normal way when entering the date through a literal value we make sure that it matching our NLS_DATE_FORMAT

SQL> ALTER SESSION SET NLS_DATE_FORMAT=’DD/MM/YYYY’;

Session altered.

SQL> INSERT INTO datetest VALUES (’06/12/2006′);

1 row created.

or we use TO_DATE to convert out literal value to date first

SQL> INSERT INTO datetest
VALUES (TO_DATE(’20061206′,’YYYYMMDD’));

1 row created.

Now today I came across another way by using “ANSI date literal”

ANSI - American National Standards Institute

SQL> INSERT INTO datetest VALUES( DATE ‘2006-12-06′);

1 row created.

It contains no time element and is formatted exactly as follows:

DATE ‘YYYY-MM-DD’


Tags: Oracle Administration · Oracle Database

0 responses so far ↓

  • There are no comments yet...Kick things off by filling out the form below.

Leave a Comment