Select date range using SQL

Question

Hi everyone, I'm trying to select a date range using SQL and I've come across a few issues:

When I run something along the lines of:

SELECT ... as edate ...    
WHERE edate 
   BETWEEN To_Date('10/15/2010', 'MM/DD/YYYY') 
   AND To_Date('10/15/2011', 'MM/DD/YYYY')

it will come back with a

ORA-01848: not a valid month

. The table itself contains a full date value along the lines of MM/DD/YYYY HH:mm:ss. Could it be that because I'm doing a SELECT edate where edate isn't 'cast' as a date it can't match it?

When I run something along the lines of:

WHERE date BETWEEN '10/15/2010' AND '12/15/2011'

It will select the right dates if they fall in the range of 10/15/2010 to 12/31/2010 but not the ones from 1/1/2011 to 12/15/2010. In other words it won't wrap around the year.

Answer 1

Try this. WHERE to_Date(edate,'MM/DD/YYYY')

I'm a SQL guy, but I think if you convert eDate you should be good.

Answer 2

To answer my own question, yes, in order to compare apples to apples, I needed to

select to_date(value, 'MM/DD/YYYY HH24:MI:SS') ... as edate

and also use

To_Date('10/15/2011', 'MM/DD/YYYY HH24:MI:SS')

in order to make an appropriate date to date comparison.