CODEWITHSUNDEEP
javaoracle-databasedatejpa
Genaut
Genaut

Reputation: 1850

Select data between dates from oracle (date type)

I have this data in my DB and I want only select between days (not inclusive. From this data, only want extrac from day 14.

ID          REFERENCE       REQUEST_DATE (TYPE DATE)
2141777690  1500541052868   14/04/18
2141777750  1500541052868   14/04/18
2141777810  1500541052868   14/04/18
2141777870  1500541052868   14/04/18
2141777930  1500541052868   14/04/18
2141778080  1500541052868   14/04/18
2141778470  1500541052868   13/04/18
2141778590  1500541052868   13/04/18
2141778980  1500541052868   13/04/18
2141779010  1500541052868   13/04/18
2141779100  1500541052868   13/04/18

I tried this (11 results):

select count(entitypoll0_.ID) as col_0_0_ from MY_TABLE entitypoll0_
where entitypoll0_.POLL_ID=1500541052868 
and entitypoll0_.REQUEST_DATE > TO_DATE('2018-04-13','YYYY-MM-DD') 
and entitypoll0_.REQUEST_DATE < TO_DATE('2018-04-15','YYYY-MM-DD')

I tried this (11 results):

select count(entitypoll0_.ID) as col_0_0_ from MY_TABLE entitypoll0_
where entitypoll0_.POLL_ID=1500541052868 
and (entitypoll0_.REQUEST_DATE between TO_DATE('2018-04-13','YYYY-MM-DD') and TO_DATE('2018-04-15','YYYY-MM-DD'));

By the way, I only have 5 results on the day 14, what I making wrong? I are not saving the time and I thought that this should be enough for this type of query.

Thanks

Upvotes: 0

Views: 67

Answers (2)

APC
APC

Reputation: 146239

"I are not saving the time"

Are you sure? The most obvious explanation is that the values of REQUEST_DATE contain a time element. You can check this quite easily by truncating the column which removes the time element. This should give uou the result you expect.

select count(entitypoll0_.ID) as col_0_0_ 
from MY_TABLE entitypoll0_
where entitypoll0_.POLL_ID=1500541052868 
and trunc(entitypoll0_.REQUEST_DATE) > TO_DATE('2018-04-13','YYYY-MM-DD') 
and trunc(entitypoll0_.REQUEST_DATE) < TO_DATE('2018-04-15','YYYY-MM-DD')

" I was assuming that using Date type (instead of timestamp, etc..) would give me the date data without time"

Oracle's DATE datetype is actually a datetime, which confuses many people new to the database. TIMESTAMP is more precise (fractional seconds) and supports timezones.

Upvotes: 2

Kaushik Nayak
Kaushik Nayak

Reputation: 31676

From this data, only want extract from day 14.

Just use AND REQUEST_DATE >= DATE '2018-04-14' and REQUEST_DATE < DATE '2018-04-14' + 1

it should be fine.

Upvotes: 1

Related Questions