CODEWITHSUNDEEP
sqloracle-databasedate-arithmetic
Harshit Wadhera
Harshit Wadhera

Reputation: 29

Get the data with a between condition

I have query 

select * from TABLE_NAME 
where date 
between to_date('26/01/2016', 'dd/mm/yyyy hh24:mi') 
and     to_date('26/01/2016', 'dd/mm/yyyy hh24:mi');

01/26/2016 11:59:32 PM

above one is the date which need to fetch when running the query

But when i running the same query it was not able to filter. Don't know why?

I don't know where i am doing wrong.

Upvotes: 0

Views: 33

Answers (1)

Lalit Kumar B
Lalit Kumar B

Reputation: 49112

between to_date('26/01/2016', 'dd/mm/yyyy hh24:mi') and to_date('26/01/2016', 'dd/mm/yyyy hh24:mi')

You do not have any time portion in to_date('26/01/2016', 'dd/mm/yyyy hh24:mi') at all. You only have date portion, so the filter will ignore the time portion and return all the rows for that date. The time portion in your query is:

SQL> alter session set nls_date_format='DD/MM/YYYY HH24:MI:SS';

Session altered.

SQL> SELECT to_date('26/01/2016', 'dd/mm/yyyy hh24:mi') FROM dual;

TO_DATE('26/01/2016
-------------------
26/01/2016 00:00:00

To filter the rows with 01/26/2016 11:59:32 PM you need:

TO_DATE('01/26/2016 11:59:32 PM' ,'DD/MM/YYYY HH:MI:SS PM')

Upvotes: 1

Related Questions