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 11.2.0.3 to 12.1.0.2 [Release 11.2 to 12.1]
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

 

Changes

 

Cause

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

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


In this Document
Symptoms
Changes
Cause
Solution
References


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