Oracle DB Query | where date greater than or equal to

[gduron]

Hello All,

I have a table that is located in an oracle database. The table has a number of columns. I'm able to successfully connect to the database and query without any problems as long as I do not try to filter on a date column. But as soon as I try to say "where date >= '1/1/2010' (date can be anyting), the record set is empty. I know there should be records returned, but for some reason my date query is not working.

Any ideas would be greatly appreciated.

Example 1 (works no problem):

str_sql = "select * from table"
rs.Open str_sql, con

Example 2 (returns empty recordset):

str_sql = "select * from table where startdate>='1/1/2010'"
rs.Open str_sql, con

One final thing is when I look at the properties of this column, the defined size is "16"

Thanks in advance.

 

[tweedle]

Are you sure it's not defined as a date-time?
such that you may need to convert your parameter to date-time to get accurate an return.

 

[gduron]

Of course, I spend two days looking for a solution and then I find one that works right after I post on this site.


I'm not sure why this works, but apparently there is a function that helps define the type in the query for Oracle called "to_date".

If anyone has any idea of why this function is necessary, I'd appreciate it.

str_sql = "select * from table where startdate>=to_date('1/1/2010','mm/dd/yyyy')"
rs.Open str_sql, con

 

[tweedle]

The field is defined as a date-time stamp. Handing string parameters to compare against dates doesn't generally work because dates are generally stored as floating point numeric values. Trying to use an air filter on the oil pump, so to speak.

 

[gduron]

So if I understand correctly, the to_date function actually converts the string '1/1/2010' to a the numeric equivalent that the date is actually stored as?

 

[tweedle]

Yep!

Though I've seen data definitions differ in the same database, by the same vendor.
Sometimes they can be date-time, sometimes as various string patterns. yyyy-mm-dd, cyymmdd, etc.