Improve company productivity with a Business Account.Sign Up

x
?
Solved

Oracle, Timestamp

Posted on 2012-03-15
9
Medium Priority
?
836 Views
Last Modified: 2012-03-16
how do i just drop the 'with time zone' value from a 'timestamp with time zone' field?
tried this :
select to_timestamp (' 3/15/2012 4:18:53.003000 AM -04:00','mm/dd/yyyy hh:mi:ssxff am tzr') from dual;
i get a ORA-01821..
I dont want to 'cast' the value, just drop the 'timezone'.
0
Comment
Question by:Rao_S
  • 4
  • 4
9 Comments
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 1600 total points
ID: 37726337
you can't  

"timestamp with time zone" is a distinct data type from "timestamp"
0
 
LVL 79

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 400 total points
ID: 37726341
The  -04:00 is not a time zone region.  TZR is the mask for a region.

Now for the question: are you wanting to convert a string into a time zone like in the question or do you have a times stamp column that you want to remove the region from?
0
 

Author Comment

by:Rao_S
ID: 37726351
i have a table with 'timestamp with time zone' with lots of data. the 'with time zone' is not relevant to us, suppose i want to change that field to just 'timestamp' how do i do it?
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 1600 total points
ID: 37726353
if you try to insert a plain timestamp value without a time zone,  it will work but you'll implicitly include your sessiontimezone


to_timestamp('3/15/2012 4:18:53.003000 AM','mm/dd/yyyy hh:mi:ssxff am')
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 1600 total points
ID: 37726364
you can't change the datatype while it has time zone data in it,  but you can create a new column, populate it and then drop the old and change the name

alter table ee add (newcolumn timestamp);
update ee set newcolumn = oldcolumn;
alter table ee drop column oldcolumn;
alter table ee rename column newcolumn to oldcolumn;
0
 

Author Comment

by:Rao_S
ID: 37726377
ok, i see.. thank you..
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 1600 total points
ID: 37726379
look for indexes or constraints on the column that need to be rebuilt
0
 

Author Comment

by:Rao_S
ID: 37726396
ok..
0
 

Author Closing Comment

by:Rao_S
ID: 37729125
thank you!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

602 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question