0

I have table_name on oracle 12c which has column_name1 with data type "TIMESTAMP(6) WITH TIME ZONE". I am using SQL Developer.

When I select anything from this column_name1 using SQL Developer, I see the date like: 19-SEP-17 03.19.55.000000000 PM +00:00 which is in UTC.

If I use same format in my SQL and give it as string in where clause it works fine. Example:

select column_name1 from table_name where column_name1 = '19-SEP-17 03.19.55.000000000 PM +00:00';

But I know it is recommended to convert string to date in situations like this. So when I am trying to do this using TO_TIMESTAMP, I am failing to find the correct format to add in the SQL.

What I have tried is:

select column_name1 from table_name where column_name1 = TO_TIMESTAMP('19-SEP-17 03.19.55.000000000 PM +00:00', 'DD-MON-YYYY HH:MI:SS.FF A.M. TZH:TZM');

And even though A.M./PM is already there, I still get error as: AM/A.M. or PM/P.M. required

I have tried adding 'nls_date_language=american' as well but still get same error.

What am I missing here? How can I change the TO_TIMESTAMP format so I can convert this string to TIMESTAMP?

300
  • 965
  • 1
  • 14
  • 53

1 Answers1

3

Are you, actually, using TO_TIMESTAMP_TZ - as you should, since the column is timestamp WITH TIME ZONE, and the input string and the format model are both for timestamp WITH TIME ZONE? (If you were using TO_TIMESTAMP like that you would get a different error, something like "date format not recognized" - since you can't have timezone format elements in the format model for a timestamp WITHOUT timezone).

Assuming that is the case: The problem is caused by passing PM (without dots) in the input string, but having A.M. (with dots) in the format model. Use the same format in both places - either with dots, or without them, but be consistent.

In some places Oracle doesn't fuss over mismatched separators, but in this case it does. If you like you can hunt through the documentation to find out exactly which substitutions Oracle will attempt; but that sounds like an academic exercise. Just be consistent between the input string and the format model and you won't have any problems.

  • Thank you for response and sorry for checking this very late. I ended up changing the default timestamp display format in Oracle SQL Developer and changed it to `YYYY-MM-DD HH24.MI.SSXFF` and for timestamp with timezone `YYYY-MM-DD HH24.MI.SSXFF TZR`. I did it from tools -> preferences -> Database -> NLS – 300 Oct 30 '17 at 19:17
  • @300 - Most developers prefer the 24 hour format (less confusion, less opportunity for annoying rules like this one). AM/PM would be used for non-tech "consumer" reports. –  Oct 30 '17 at 22:08