Home » SQL & PL/SQL » SQL & PL/SQL » to_timestamp_tz (Oracle 12c Windows10)
to_timestamp_tz [message #681856] Fri, 04 September 2020 08:42 Go to next message
pstanand
Messages: 133
Registered: February 2005
Location: Chennai,India
Senior Member
Hi I have a table with column created with TIMESTAMP(6) WITH TIME ZONE as data type.
I got the data in the csv file for this column as 6/18/2020 10:48:25.000000 PM -06:00.

However when I tried to import data through sql developer I got
ORA-01843: not a valid month.

Could you please help me here to get the data inserted into my table.

Appreciate your help.

Thanks.
create table prod_cat(prod_id number,created timestamp(6) with time zone.
Re: to_timestamp_tz [message #681857 is a reply to message #681856] Fri, 04 September 2020 09:35 Go to previous messageGo to next message
John Watson
Messages: 8640
Registered: January 2010
Location: Global Village
Senior Member
orclz>
orclz> select to_timestamp_tz('6/18/2020 10:48:25.000000 PM -06:00') from dual;
select to_timestamp_tz('6/18/2020 10:48:25.000000 PM -06:00') from dual
                       *
ERROR at line 1:
ORA-01843: not a valid month


orclz>
orclz> alter session set nls_timestamp_tz_format='MM/DD/YYYY HH:MI:SS.FF AM TZH:TZM';

Session altered.

orclz>
orclz> select to_timestamp_tz('6/18/2020 10:48:25.000000 PM -06:00') from dual;

TO_TIMESTAMP_TZ('6/18/202010:48:25.000000PM-06:00')
---------------------------------------------------------------------------
06/18/2020 10:48:25.000000000 PM -06:00

orclz>
icon14.gif  Re: to_timestamp_tz [message #681860 is a reply to message #681857] Fri, 04 September 2020 10:11 Go to previous messageGo to next message
pstanand
Messages: 133
Registered: February 2005
Location: Chennai,India
Senior Member
Thank you very much John....it worked...
Smile Smile
Re: to_timestamp_tz [message #681862 is a reply to message #681860] Fri, 04 September 2020 10:30 Go to previous messageGo to next message
Michel Cadot
Messages: 68043
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Alternatively you can spe cify the format in TO_TIMESTAMP_TZ function:
SQL> select to_timestamp_tz('6/18/2020 10:48:25.000000 PM -06:00') from dual;
select to_timestamp_tz('6/18/2020 10:48:25.000000 PM -06:00') from dual
                       *
ERROR at line 1:
ORA-01843: not a valid month


SQL> select to_timestamp_tz('6/18/2020 10:48:25.000000 PM -06:00',
  2                         'MM/DD/YYYY HH:MI:SS.FF AM TZH:TZM')
  3  from dual;
TO_TIMESTAMP_TZ('6/18/202010:48:25.000000PM-06:00','MM/DD/YYYYHH:MI:SS.FFAM
---------------------------------------------------------------------------
18/06/2020 22:48:25.000 -06:00

1 row selected.
Re: to_timestamp_tz [message #681866 is a reply to message #681860] Fri, 04 September 2020 12:54 Go to previous message
EdStevens
Messages: 1350
Registered: September 2013
Senior Member
pstanand wrote on Fri, 04 September 2020 10:11
Thank you very much John....it worked...
Smile Smile
Now, the question is, do you understand why it worked?

In your failed example you were relying on the default timstamp mask, as defined by NLS_TIMESTAMP_FORMAT. In John's successful example, he supplied the format mask, overriding NLS settings.

For a deeper dive, read this: https://edstevensdba.wordpress.com/2011/04/07/nls_date_format/
Previous Topic: xml to json conversion
Next Topic: Foreign key
Goto Forum:
  


Current Time: Thu Dec 02 17:07:18 CST 2021