CODEWITHSUNDEEP
sqloracle-databasedate
user2470075
user2470075

Reputation: 169

Selecting data between two dates

I want to get a data for '20130501'

Following is the query where I put '20130501' for start date, and '20130502' for the end date.

SELECT A.NAME, A.GENDER
                FROM TABLE A
              WHERE 
               A.DTM >= TO_DATE('20130501','YYYYMMDD')
              AND A.DTM <= TO_DATE('20130502','YYYYMMDD')

The query above gives the data for '20130501' fine. But now I want to put in '20130501' for both start and end date input data. Below is the query

SELECT A.NAME, A.GENDER
                FROM TABLE A
              WHERE 
               A.DTM >= TO_DATE('20130501','YYYYMMDD')
              AND A.DTM <= TO_DATE('20130501','YYYYMMDD')

When I run the query, I get no data. I think it's because DB reads the both '20130501' as the same time. But I want it to be '20130501 00:00' to '20130501 24:00'

So can anyone fix the query?

Upvotes: 1

Views: 331

Answers (3)

Sam
Sam

Reputation: 1366

Another way to solve it is to trunc the date your are comparing with:

SELECT A.NAME, A.GENDER
FROM TABLE A
WHERE TRUNC(A.DTM) BETWEEN TO_DATE('20130501','YYYYMMDD') AND TO_DATE('20130501','YYYYMMDD');

Edit: if you only want a single day:

SELECT A.NAME, A.GENDER 
FROM TABLE A
WHERE TRUNC(A.DTM) = TO_DATE('20130501','YYYYMMDD');

Edit about performance: in my experience, TRUNC is rather fast and doesn't make a noticable difference if there are no indices. That being said: TRUNC(A.DTM) won't use indices on A.DTM. You can put an index on TRUNC(A.DTM) though.

Upvotes: 0

araknoid
araknoid

Reputation: 3125

Instead of using the > and < for a date range you should use the BETWEEN since it is range inclusive:

SELECT A.NAME, A.GENDER
FROM TABLE A
WHERE A.DTM BETWEEN TO_DATE('20130501','YYYYMMDD') AND TO_DATE('20130502','YYYYMMDD');

http://www.techonthenet.com/sql/between.php

Since the solution with TO_DATE does not support milliseconds you can convert the TO_DATE into TO_TIMESTAMP that support the milliseconds:

SELECT A.NAME, A.GENDER
    FROM TABLE A
    WHERE A.DTM BETWEEN TO_DATE('20130501'||' 00:00','YYYYMMDD HH24:MI') AND TO_TIMESTAMP('20130501'||' 23:59:59.999','YYYYMMDD HH24:MI:SS.FF');

but if you need greater precision, you should work with the date difference:

SELECT A.NAME, A.GENDER
FROM TABLE A
WHERE (A.DTM - TRUNC(TO_DATE('20130501','YYYYMMDD')) BETWEEN 0 AND 1;

0 = A.DTM is equal to 20130501 00:00

1 = A.DTM is equal to 20130502

Upvotes: 2

Joachim Isaksson
Joachim Isaksson

Reputation: 181077

Just add a day to your second date, and use < for the comparison;

SELECT A.NAME, A.GENDER
FROM A
WHERE A.DTM >= TO_DATE('20130501','YYYYMMDD')
  AND A.DTM <  TO_DATE('20130501','YYYYMMDD')+1

An SQLfiddle to test with.

Upvotes: 7

Related Questions