SQL Language (MOSC)

MOSC Banner

Oracle Timestamp before 1970 - seconds value incorrect

edited Jul 11, 2014 5:10AM in SQL Language (MOSC) 1 commentAnswered

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

Column TS2 below shows the problem.

This is seen in Oracle SQL Developer 4.0.0.13, java Platform 1.7.0_45, but the correct result is returned in SQL*Plus

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

Has this problem been seen before? Is there a patch?

<span class="kwd" style="color: #00008b; background: transparent;">alter</span><span class="pln" style="background: transparent;"> session </span><span class="kwd" style="color: #00008b; background: transparent;">set</span><span class="pln" style="background: transparent;"> nls_timestamp_format</span><span class="pun" style="background: transparent;">=</span><span class="str" style="color: #800000; background: transparent;">'SS'</span><span class="pun" style="background: transparent;">;</span>
Tagged:

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center