Skip to Main Content
  • Questions

  • converting TIMESTAMP(6) to TIMESTAMP(0)

Breadcrumb

Connor and Chris will both be at AI World from October 12 to October 17 , the premier Oracle conference of 2025. If you're in Vegas, please come say Hi or pop into our sessions

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

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Design

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