Skip to Main Content
  • Questions
  • converting TIMESTAMP(6) to TIMESTAMP(0)

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Lakshman.

Asked: June 20, 2018 - 7:02 pm UTC

Last updated: June 25, 2018 - 3:37 pm UTC

Version: 12c

Viewed 10K+ times! This question is

You Asked

Currently I have a column with datatype TIMESTAMP(6) but now i have a requirement to change it to TIMESTAMP(0).
Because we cannot decrease the precision,

ORA-30082: datetime/interval column to be modified must be empty to decrease fractional second or leading field precision

So, am following this process:

1. Copy data to temp table.
2. drop and re-create table with new datatype.
3. loading data to actual table from temp table using this below logic for this particular column
TO_CHAR (UPDATE_DTTM, 'YYYY-MON-DD HH24:MI:SS')

But am getting "ORA-01843: not a valid month"

But when am writing SELECT UPDATE_DTTM,TO_CHAR (UPDATE_DTTM, 'YYYY-MON-DD HH24:MI:SS') from TABLE1;
no issue. data is returning.

data before the change Timestamp(6)
1 01-FEB-17 04.39.54.000000000 PM
2 20-JAN-17 05.12.20.000000000 PM
3 08-JAN-09 08.56.54.000000000 PM
Data after the change: (Timestamp(0)
1 01-FEB-17 04.39.54
2 20-JAN-17 05.12.20
3 08-JAN-09 8.56.54

and Chris said...

Hold on a second there. You're inserting data to a timestamp by converting it to_char?!

You've hit the implicit conversion jackpot!

The database will have to transform that back into a timestamp.

So what you really have is:

to_timestamp ( TO_CHAR (UPDATE_DTTM, 'YYYY-MON-DD HH24:MI:SS') ) 


And, because it's implicit you've not specified a format mask. So it'll use your client's NLS settings. Not good.

You can avoid this by casting the value to a timestamp(0):

select systimestamp, cast( systimestamp as timestamp(0) )
from   dual;

SYSTIMESTAMP                         CAST(SYSTIMESTAMPASTIMESTAMP(0))   
21-JUN-2018 03:25:28.914751000 -07   21-JUN-2018 03:25:29.000000000  

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

implicit cast

A reader, June 25, 2018 - 1:07 pm UTC

wouldn't the insert do an implicit cast if you just selected the original column?
Chris Saxon
June 25, 2018 - 3:37 pm UTC

Yep, I was just covering off the explicit version. It's always safer :)

Lakshman Kumar, June 26, 2018 - 7:57 pm UTC


Lakshman Kumar, June 26, 2018 - 8:04 pm UTC


More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.