2

The following SQL run on Oracle 11g returns an incorrect 'seconds' value for dates before 1970-01-01 when fractional seconds are present.

Is there something wrong with the SQL? 

alter session set nls_timestamp_format='SS';

select
       to_timestamp('1969-12-31 23:59:59 0','YYYY-MM-DD HH24:MI:SS FF') TS1,
       to_timestamp('1969-12-31 23:59:59 1','YYYY-MM-DD HH24:MI:SS FF') TS2,
       to_timestamp('1970-01-01 00:00:00 1','YYYY-MM-DD HH24:MI:SS FF') TS3
from dual;

TS1  TS2  TS3
59   58   00
jmlemetayer
  • 4,774
  • 1
  • 32
  • 46
cp2014
  • 21
  • 2
  • I can't reproduce this on 11.2.0.3.0 - 64bit. The result I get is 59, 59, 00. I suggest that you contact Oracle support. – Allan Jun 27 '14 at 14:01
  • Might be helpful to know the platform on which you're running Oracle – David Faber Jul 05 '14 at 03:01
  • @David, The SQL was run on SQL Developer 4.0.0.13, java Platform 1.7.0_45 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production OPATCH_VERSION:11.2.0.3.6 MS Windows Server 2008 R2 Standard Service Pack 1 – cp2014 Jul 07 '14 at 13:40
  • @Allan, thanks for the suggestion. I have posted the question to Oracle Support – cp2014 Jul 07 '14 at 13:41
  • I have found that the correct results are produced in SQL+ – cp2014 Jul 07 '14 at 13:42
  • i am unable to reproduce this eitehr. sql developer 4.1.3.20.78 connected to oracle 11.2.0.1 – ShoeLace May 16 '16 at 15:58

0 Answers0