Oracle - About Inserting Date Values

We all know, that the default format support by Oracle (from 9i) is DD-Mon-RR.

Does this mean, we need to give the same format, while inserting records to the table?.

If your answer is Yes, then continue reading the article.

In general, oracle assumes that the date needs to be given in dd, followed by Mon followed by RR/YYYY.

So in between the data components, we are allowed to use any character. So the following insert statements

will insert records in to the table dat1(with 1 column, called col1 of type date), without any error.

Note: FYI, In all the cases, to_date( ) function is not used.

insert into dat1 values('12?Jan?2007')

insert into dat1 values('12/Jan?2007')

insert into dat1 values('12!Jan#2007')

insert into dat1 values('12$jan%2007')

insert into dat1 values('12^Jan&2007')

insert into dat1 values('12/Jan?2007')

SQL> select * from dat1;

COL1

---------

12-JAN-07

12-JAN-07

12-JAN-07

12-JAN-07

12-JAN-07

12-JAN-07

6 rows selected.

Thanks & Regards

Vivek Member - Cloud Nine

 

0 comments: