Changing DATE Format 1

[UDIT]

Hi,

When a new Table is being created in Oracle 8 and a column for datatype - Date is to be created , the standard format for inserting VALUES into the column after the table has been created is - 'dd-mon-yy' , here the Century is not entered , only the last two digits of the year are entered like - '12-Jan-95' , IF ONE TRIES TO ENTER THE FULL YEAR LIKE '12-Jan-1995' THEN IT IS NOT ACCEPTED AND AN ERROR IS SHOWN .

How can one change the date format while creating a table so that the full year - '12-Jan-1995' could be entered and it is accepted by Oracle ?

Also , How one can UPDATE prevoiusly created columns with date format - '12-Jan-95' to '12-Jan-1995' ?

Thanks.

 

[karluk]

As I recall the way to enter dates with different formats is to use the to_date function with a date format. For example

to_date('12-JAN-1995','DD-MON-YYYY')

 

[prosenjit]

SQL> insert into tt
2 values('10-jan-00')
3 /

1 row created.

SQL> insert into tt
2 values('10-jan-2000');

1 row created.

SQL>

I am not sure why the same should not happen with you. Try it again...

 

[CRoberts]

Do not try and update a column just to change the way a date is displayed. Oracle stores a date in a 7 byte format and translates from the default format, usually the format mask is 'dd-mon-yy'. Oracle simply supply the missing elements (minutes, seconds, and century). This default format can be changed, either for a session or the db. To change it for a session just use:

ALTER SESSION Set NLS_DATE_FORMAT='DD-MON-YYYY';

If you try to insert a date that is not in the default format, then you must use the TO_DATE function. However, beginning with Oracle 8i, this changed somewhat. Oracle 8i will take either the default without the century, or with it. But, now the minutes and seconds that used to be appended are not. So, with Oracle 8i if you want minutes and seconds, you must supply them via a TO_DATE function.