CODEWITHSUNDEEP
sqloracle-databasedate
monvic
monvic

Reputation: 28

Issue with date comparison in Oracle

I am doing a date comparison in oracle. When I execute the code below

select tr.x, tbi.y, to_char(tr.UPDATED_AT,'dd-mon-yyyy')
from  tbi, tb, tr
where
tbi.id=tb.id and
tb.id=tr.id  and 
tr.updated_at = to_date('23/02/2016', 'dd/MM/yyyy')

No row is selected

But when I execute the following query

select tr.x, tbi.y, to_char(tr.UPDATED_AT,'dd-mon-yyyy')
from  tbi, tb, tr
where
tbi.id=tb.id and
tb.id=tr.id 
and 
tr.updated_at > to_date('23/02/2016', 'dd/MM/yyyy')

I get this result

trx.x  tbi.y TO_CHAR(TR.UPDATED_AT,'DD-MM-YYYY')
123456  0    23-02-2016
12345   0    23-02-2016
123     0    23-02-2016
123123  0    23-02-2016

Why is the > operator showing dates which are equal and = is not showing the equal dates ?

Upvotes: 1

Views: 114

Answers (5)

user3104783
user3104783

Reputation: 19

The clause 

tr.updated_at = to_date('23/02/2016', 'dd/MM/yyyy')

is not returning any results due to the fact that the Updated_at field in the table TR being a date time field which stores a timestamp.

Eg: 23-02-2016 00:00:00

hence when you use the Equals operator, it returns false due to the missing timestamp on the RHS of the clause.

For performance reasons and to avoid ambiguity, I would advise the use of between with a date range or Trunc function on Tr.Updated_at.

eg: Trunc(tr.updated_at) = to_date('23/02/2016', 'dd/MM/yyyy')

Upvotes: 0

Bhaskar
Bhaskar

Reputation: 9

Because updated_at column have the date with minutes and seconds. 
You are trying to fetch records for which updated_at has the value 
23/02/2016 00:00:00

Try this for desired output :

select tr.x, tbi.y, to_char(tr.UPDATED_AT,'dd-mon-yyyy') from tbi, tb, tr where tbi.id=tb.id and tb.id=tr.id and to_char(tr.updated_at,'dd/mm/yyyy') = to_date('23/02/2016', 'dd/MM/yyyy')

Upvotes: 0

Erich Kitzmueller
Erich Kitzmueller

Reputation: 36977

Using TRUNC, as suggested by other answers, makes it impossible to used indexes on this field (except function-based indexes). For that reason, you might prefer

tr.updated_at between to_date('23/02/2016', 'dd/mm/yyyy')
                  and to_date('23/02/2016 23:59:59', 'dd/mm/yyyy hh24:mi:ss')

Upvotes: 0

sagi
sagi

Reputation: 40481

You should use TRUNC()

select tr.x, tbi.y, to_char(tr.UPDATED_AT,'dd-mon-yyyy')
from  tbi, tb, tr
where
tbi.id=tb.id and
tb.id=tr.id 
and 
trunc(tr.updated_at) = to_date('23/02/2016', 'dd/MM/yyyy')

The problem with your query(I'm guessing by the look of it) your updated_at format is dd/mm/yyyy hh24:mi:ss .

So, 23/02/2016 is not equal to 23/02/2016 20:00:05 . (A default hh24:mi:ss of a date is 00:00:00)

Trunc() makes the date formated like dd/mm/yyyy and ignore the hours

Upvotes: 1

Mehdi
Mehdi

Reputation: 7403

problem

tr.updated_at = to_date('23/02/2016', 'dd/MM/yyyy')

returns you only the results where updated_at equals 23/02/2016 00:00:00

solution

Try the following instead: 

trunc(tr.updated_at) = to_date('23/02/2016', 'dd/MM/yyyy')

cf trunc function documentation.

Upvotes: 0

Related Questions