OraFAQ Forum: SQL & PL/SQL » to_timestamp

Home » SQL & PL/SQL » SQL & PL/SQL » to_timestamp_tz (Oracle 12c Windows10)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

to_timestamp_tz [message #681856]

Fri, 04 September 2020 08:42

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.

Report message to a moderator

Re: to_timestamp_tz [message #681857 is a reply to message #681856]

Fri, 04 September 2020 09:35

John Watson
Messages: 8804
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>

Report message to a moderator

Re: to_timestamp_tz [message #681860 is a reply to message #681857]

Fri, 04 September 2020 10:11

pstanand
Messages: 133
Registered: February 2005
Location: Chennai,India

Senior Member

Thank you very much John....it worked...
Smile

Report message to a moderator

Re: to_timestamp_tz [message #681862 is a reply to message #681860]

Fri, 04 September 2020 10:30

Michel Cadot
Messages: 68418
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.

Report message to a moderator

Re: to_timestamp_tz [message #681866 is a reply to message #681860]

Fri, 04 September 2020 12:54

EdStevens
Messages: 1375
Registered: September 2013

Senior Member

pstanand wrote on Fri, 04 September 2020 10:11

Thank you very much John....it worked...

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/

Report message to a moderator

Previous Topic:	xml to json conversion
Next Topic:	Foreign key

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Mar 28 04:01:37 CDT 2023