Odbc Paradox Driver WHERE clause Date

Question

I am using an Odbc driver with Paradox. I have a table with a date in it. I am trying to query by the date in that column. I can't seem to get the where clause to work. I can get the record searching by ints, but I don't know how to use the date time.

OdbcCommand comm= new OdbcCommand("SELECT * FROM  [Journal]  WHERE" + 
                                  "[Date] = 04/02/2009 ",
                                  new OdbcConnection(@"Driver={Microsoft Paradox Driver (*.db )};DriverID=538;Fil=Paradox 5.X;DefaultDir=d:\\ics\\tables\\HISTORY;Dbq=d:\\ics\\tables\\HISTORY;CollatingSequence=ASCII"));

OdbcDataAdapter adapt = new OdbcDataAdapter(comm);
DataTable table = new DataTable();
adapt.Fill(table );

Answer 1

Look for Date/Time functions in Paradox that will convert a string to a date. It is very tricky to use a String as a date like in your SQL. There must be some functions like TO_DATE or similar that will properly format it for you.

Answer 2

Ok I figured it out.

...where   [Date] = {d 'yyyy-MM-dd'} AND...

In odbc you have to use the {} to define an object, and the d defines the format of the date.

You can use the following.

{d 'value'} yyyy-mm-dd

{t 'value'} hh:mm:ss

{ts 'value'} yyyy-mm-dd hh:mm:ss