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

Last updated on DECEMBER 06, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.3 and later
Information in this document applies to any platform.

Symptoms

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;
D
--------------------------
25-FEB-2014:11:59:00
25-FEB-2014:11:58:00

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

 

Cause

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 hundreds of Community platforms