• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 826
  • Last Modified:

Oracle, Timestamp

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
Rao_S
Asked:
Rao_S
  • 4
  • 4
5 Solutions
 
sdstuberCommented:
you can't  

"timestamp with time zone" is a distinct data type from "timestamp"
0
 
slightwv (䄆 Netminder) Commented:
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
 
Rao_SAuthor Commented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
sdstuberCommented:
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
 
sdstuberCommented:
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
 
Rao_SAuthor Commented:
ok, i see.. thank you..
0
 
sdstuberCommented:
look for indexes or constraints on the column that need to be rebuilt
0
 
Rao_SAuthor Commented:
ok..
0
 
Rao_SAuthor Commented:
thank you!
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now