CODEWITHSUNDEEP
databaseoracle-databasetimestamp
Nishant Shrivastava
Nishant Shrivastava

Reputation: 523

Oracle Timestamp precision not obeyed

I created a table in Oracle 

CREATE TABLE clock
(
Year TIMESTAMP(3)
);

I inserted a value and hope that it will be stored with 3 decimal digits precision.

insert
into clock (Year)
values ( TIMESTAMP '2014-01-01 00:00:00.000');
commit;

However when i run a select query it gives output with 9 decimal precison

select * from clock;

Output:

01-JAN-14 12.00.00.000000000 AM

Expected output

01-JAN-14 12.00.00.000 AM

What am i doing wrong here?

Upvotes: 0

Views: 435

Answers (2)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59446

The problem is your default TIMESTAMP format. Try this then is should work.

ALTER SESSION SET nls_timestamp_format = 'DD-MON-YY HH:MI:SSXFF3 AM';

Upvotes: 1

Luke Woodward
Luke Woodward

Reputation: 64949

The data is being stored with three decimal digits precision, it's just not being displayed with 3 digits precision, presumably because of your database's NLS settings.

If you insert a number with more than 3 digits' precision, the extra digits get truncated. For example, try running

insert into clock (Year) values (TIMESTAMP '2014-01-01 12:34:56.123456789');

Then

select * from clock;

should return

01-JAN-14 12.00.00.000000000 AM
01-JAN-14 12.34.56.123000000 PM

If you don't want to show the extra digits, then try something like the following instead:

select to_char(Year, 'DD-MON-RR HH.MI.SSXFF3 AM') from clock;

Upvotes: 2

Related Questions