Oracle timestamp with timezone in where clause issue

Question

I have the following sql query with

select * from MY_TABLE
 MY_COL >= to_timestamp_tz('08-03-17 07:25:00.0000 EST', 'dd-mm-yy hh24:mi:ss.ff TZR') and 
 MY_COL <= to_timestamp_tz('08-03-17 09:08:00.0000 EST', 'dd-mm-yy hh24:mi:ss.ff TZR')

Where there are atleast the following 2 entries(as displayed in sql developer).

03-AUG-17 07.25.51.576000000 AM AMERICA/NEW_YORK
03-AUG-17 07.31.33.553000000 AM AMERICA/NEW_YORK

My query returns no entries. I would expect to get both these entries back. Is there something obvious that I am missing here?

thanks

Answer 1

Please look closely at this

MY_COL >= to_timestamp_tz('08-03-17 07:25:00.0000 EST', 'dd-mm-yy hh24:mi:ss.ff TZR') and 
 MY_COL <= to_timestamp_tz('08-03-17 09:08:00.0000 EST', 'dd-mm-yy hh24:mi:ss.ff TZR')

The date string is: '08-03-17 ... and the format string is: 'dd-mm-yy ....

• dd- is DAY = 08
• mm - is MONTH = 03
• yy - is YEAR = 17

Please check in the documentation: format models

So, your query is looking for dates that are:

• greater than 08-MARCH-2017 07:25
• lower than 8-MARCH-2017 09:08

It's not a big surprise that the query doesn't find these records:

03-AUG-17 .....
03-AUG-17 .....

since AUGUST is not MARCH