3

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

krokodilko
  • 35,300
  • 7
  • 55
  • 79
Pradyot
  • 2,897
  • 7
  • 41
  • 58
  • 1
    It looks like you may have mixed up your dd and mm specifiers. Your format is probably mm-dd-yy in this case. – MarkF Aug 04 '17 at 18:40
  • Personally I would prefer to avoid the date format ambiguity by using [timestamp literals](https://docs.oracle.com/database/121/SQLRF/sql_elements003.htm#SQLRF51062): `MY_COL >= TIMESTAMP '2017-08-03 07:25:00 EST' and ....`. (And as a further aside, make sure you really mean both `>=` and `<=`, and that you don't accidentally overlap ranges when running multiple times.) – Alex Poole Aug 04 '17 at 18:50

1 Answers1

5

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

krokodilko
  • 35,300
  • 7
  • 55
  • 79