CODEWITHSUNDEEP
sqloracle-databasetimestampto-timestamp
Pradyot
Pradyot

Reputation: 3059

Oracle timestamp with timezone in where clause issue

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

Upvotes: 3

Views: 2294

Answers (1)

krokodilko
krokodilko

Reputation: 36107

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

Upvotes: 5

Related Questions