My Oracle Support Banner

Wrong value after Alter Column from Date to Timestamp with Local Timezone (Doc ID 1999916.1)

Last updated on AUGUST 14, 2018

Applies to:

Oracle Database - Enterprise Edition - Version to [Release 11.2 to 12.1]
Information in this document applies to any platform.


When changing a column from date to timestamp with local time zone back to date,

Any Row that have minutes as 59 will not be represented in the right way

SQL> create table test_t(d date);
Table created.

SQL> insert into test_t values (trunc(current_date,'hh24')-1/24/60);
1 row created.

SQL> insert into test_t values (trunc(current_date,'hh24')-2/24/60);
1 row created.

SQL> select * from test_t;

SQL> alter table test_t modify d timestamp with local time zone;
Table altered.

SQL> alter table test_t modify d date;
Table altered.

SQL> select * from test_t;

25-FEB-2014:11:01:00   ===> wrong Value for rows with 59 minutes
25-FEB-2014:11:58:00   ===> right Value for any other minutes value





To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.