Solved

Oracle, Timestamp

Posted on 2012-03-15
9
807 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 400 total points
ID: 37726337
you can't  

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

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 100 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 400 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 400 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 400 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.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
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.

839 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