CODEWITHSUNDEEP
sqloracle-database
developer
developer

Reputation: 9478

Query not getting current date result

I am getting data from table for certain date range, but the query not getting data for today's date. Why does this happen?

select * 
from mytable 
where action_date >= to_date('01/07/2015', 'DD/MM/YYYY') 
and action_date <= to_date('22/07/2015', 'DD/MM/YYYY');

Result is not showing 22/07/2015 data.

Edit: 

ACTION_DATE TIMESTAMP(6)

Sample date in that column :

22/07/15 12:47:18.000000000 PM

Upvotes: 0

Views: 207

Answers (7)

WW.
WW.

Reputation: 24291

This is better than the (original) accepted answer, which misses a small period of time after 23:59.

select * 
from mytable 
where action_date >= to_date('01/07/2015', 'DD/MM/YYYY') 
and action_date < to_date('22/07/2015', 'DD/MM/YYYY') + 1;

Or alternatively if you don't like the +1:

select * 
from mytable 
where action_date >= to_date('01/07/2015', 'DD/MM/YYYY') 
and action_date < to_date('23/07/2015', 'DD/MM/YYYY');

Upvotes: 0

NayanSahu
NayanSahu

Reputation: 1

You can use the below query.

select * 
from mytable 
where trunc(action_date) >= to_date('01/07/2015', 'DD/MM/YYYY') 
and trunc(action_date) <= to_date('22/07/2015', 'DD/MM/YYYY');

This will perform a date-level comparison rather than one based on the date and exact time.

Upvotes: 0

developer
developer

Reputation: 9478

I have changed the query by this way, its working as expected.

SELECT *
FROM mytable 
WHERE action_date                                                    >= to_date('01/07/2015', 'DD/MM/YYYY')
AND TRUNC(to_date(TO_CHAR(action_date, 'DD/MM/YYYY'), 'DD/MM/YYYY')) <= TRUNC(to_date('22/07/2015', 'DD/MM/YYYY'));

Upvotes: 0

Jens
Jens

Reputation: 69450

Try to add the time part:

select * 
from mytable 
where action_date >= to_date('01/07/2015 00:00:00,000000000', 'DD/MM/YYYY HH24:MI:SS,FF9') 
and action_date <= to_date('22/07/2015 23:59:00,999999999', 'DD/MM/YYYY HH24:MI:SS,FF9');

If you only give the date part, the time part is automatically added with the actual time, so if the time part is later you do not get the row.

Upvotes: 2

Praveen
Praveen

Reputation: 9345

to_date('22/07/2015', 'DD/MM/YYYY')

this will equal to July, 22 2015 00:00:00

From Oracle document:

Oracle Database stores time in 24-hour format—HH:MI:SS. By default, the time in a date field is 00:00:00 A.M. (midnight) if no time portion is entered.

so action_date <=/>= will compare the data+time.

For the correct result add required time to the date field. eg:

to_date('22/07/2015 12:56', 'DD/MM/YYYY HH24:MI')

Upvotes: 1

Matt
Matt

Reputation: 15091

The easiest solution is to use '< nextday' instead of boring '<= 23:59:59'.

select * from mytable
where action_date >= to_date('01/07/2015', 'DD/MM/YYYY')    
and action_date < to_date('23/07/2015', 'DD/MM/YYYY');

Upvotes: -1

Nitu Bansal
Nitu Bansal

Reputation: 3856

it is because you are converting to_date in date, that results in 22/2/2015 00:00:00 and your action_date time for 22/7/2015 is with some time appended. you need to convert action_date in date.

select * from mytable where cast( action_date >= to_date('01/07/2015', 'DD/MM/YYYY') and cast( action_date as date) <= to_date('22/07/2015', 'DD/MM/YYYY');

Upvotes: 0

Related Questions