oracle between query not taking end date time

Ask Time：2017-07-06T17:39:15 Author：Selva

Oracle between query ignoring end date time.

Below is my table.

Id   Upload Date
1   05-JUL-17 12.02.11.309000000 PM
2   05-JUL-17 12.03.34.123000000 PM
3   05-JUL-17 12.04.15.334000000 PM

My requirement is to fetch the files between the given date time.

 select * from fileupload where uploaddate between to_DATE('05-07-17 12:02:11', 'DD-MM-YY HH24:MI:SS') and to_DATE('05-07-17 12:04:15', 'DD-MM-YY HH24:MI:SS')

select * from fileupload where uploaddate between to_timestamp('05-07-17 12:02:11', 'DD-MM-YY HH24:MI:SS') and to_timestamp('05-07-17 12:04:15', 'DD-MM-YY HH24:MI:SS')

Both queries not returns the end date time it returns only two row.

Id   Upload Date
1   05-JUL-17 12.02.11.309000000 PM
2   05-JUL-17 12.03.34.123000000 PM

But the expected result is

 Id   Upload Date
    1   05-JUL-17 12.02.11.309000000 PM
    2   05-JUL-17 12.03.34.123000000 PM
    3   05-JUL-17 12.04.15.334000000 PM

why the end date time is not fetching when using between.

Any help will be greatly appreciated!!!!

Author:Selva，eproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article：https://stackoverflow.com/questions/44945222/oracle-between-query-not-taking-end-date-time

Davide Lorenzo MARINO :

The value to_DATE('05-07-17 12:04:15', 'DD-MM-YY HH24:MI:SS') is equivalent to 05-JUL-17 12.04.15.000000000 PM that comes before 05-JUL-17 12.04.15.334000000 PM\n\nSo the third result is not extracted because is not in the range for 334 ms.\n\n\n\nTo extract all the records you need to update the query not considering the milliseconds as follow:\n\n select * from fileupload \n where cast(uploaddate as date) \n between to_DATE('05-07-17 12:02:11', 'DD-MM-YY HH24:MI:SS') \n and to_DATE('05-07-17 12:04:15', 'DD-MM-YY HH24:MI:SS')\n",

2017-07-06T09:43:24

Sidhu :

You need to use TO_TIMESTAMP().. It can handle milliseconds.\n\nTO_DATE doesn't handle milliseconds",

2017-07-06T09:40:54

oracle between query not taking end date time