CODEWITHSUNDEEP
sqloracle-databaseoracle10g
Jacob
Jacob

Reputation: 14731

Not able to fetch all records with dates condition

I have the following sql statement where I am trying to get records, the problem is I am not able to get one record even though the date is matching.

SQLFiddle 

SELECT distinct  vname,start_date
FROM   my_dates
where 
 start_date >= to_date('30-APR-2013','DD-MON-YYYY HH12:MI:SS AM') 
and start_date <= to_date('06-MAY-2013','DD-MON-YYYY HH12:MI:SS AM')

What am I doing wrong with my query?

Upvotes: 0

Views: 167

Answers (2)

mtwaddell
mtwaddell

Reputation: 189

Because you don't specify the hours, minutes or seconds in the TO_DATE portion of your criteria statement, Oracle defaults to 00:00:00, so your statement basically says to look for values where the start_date is between "30-APR-2013 00:00:00 AM" AND "06-MAY-2013 00:00:00 AM". Since "06-MAY-2013 6:59:00 AM" is outside this range, it is not returned in the results. Here are a couple ways you can fix this:

Specify the hours, minutes and seconds in your TO_DATE statement to include the entire day on the back end of the date range.

SELECT DISTINCT  vname,start_date
  FROM my_dates
 WHERE start_date BETWEEN TO_DATE('30-APR-2013','DD-MON-YYYY') 
                      AND TO_DATE('06-MAY-2013 11:59:59 PM','DD-MON-YYYY HH12:MI:SS AM')

Alternately you can do a calculation to add 23 hours, 59 minutes and 59 seconds to the 2nd date value to do the same thing as above.

SELECT DISTINCT  vname,start_date
  FROM my_dates
 WHERE start_date BETWEEN TO_DATE('30-APR-2013','DD-MON-YYYY') 
                      AND TO_DATE('06-MAY-2013','DD-MON-YYYY')+1-(1/24/60/60)

Hope that helps.

Upvotes: 2

Amit Singh
Amit Singh

Reputation: 8109

to_date('06-MAY-2013','DD-MON-YYYY HH12:MI:SS AM') will return May, 06 2013 00:00:00+0000 which is less than 06-MAY-2013 6:59:00 AM','DD-MON-YYYY HH12:MI:SS AM Because of this your are not getting the last row. Try this query...

SELECT distinct  vname,start_date
FROM   my_dates
where 
 start_date >= to_date('30-APR-2013','DD-MON-YYYY HH12:MI:SS AM') 
and to_char(start_date,'DD.MM.YYYY')  <= to_char(start_date,'DD.MM.YYYY')

Sql fiddle Demo

Upvotes: 0

Related Questions