I have an Oracle database that I created an ODBC connection with, to pull in all of the records. I want to write a script that will compare the latest record added (based on the date field) and import anything newer than that date.

The date field is set up with the time as well (e.g. 10/13/2005 7:57:17 AM). I cannot seem to get my SQL query to work to compare on this field. I assume that I will need to convert the field somehow? Does anyone know of a query that will work?

Are you using enclosing the date in single quotes?

Use a date field in FileMaker Pro to compare a date field in Oracle. If you are using timestamp in Filemaker then use timestamp field in Oracle. Is the query giving you an error or is it not returning anything? The best way is to execute the same query at sql prompt on oracle and check the results.

Are you using enclosing the date in single quotes?

Use a date field in FileMaker Pro to compare a date field in Oracle. If you are using timestamp in Filemaker then use timestamp field in Oracle. Is the query giving you an error or is it not returning anything? The best way is to execute the same query at sql prompt on oracle and check the results.

It is not returning anything. I am using single quotes, here is the query i am using:

"Select * from websearch_log Where LOG_DATE >=" & "'" & websearch_stats::date & "'"

It is a date field in Oracle and a date field in FM, but i guess I am still doing something wrong. I am fairly new to SQL.

Thanks for any help.

Edited October 14, 200520 yr by Guest

Are you storing your query in a calculation field?

If yes, place the calculation field on the layout in a browse mode and confirm that the text of the websearch_stats::date is replaced with a date value from the record. I would suggest to run the query at the sql prompt.

I think it should be:

"select * from websearch_log where log_date >='" & websearch_stats::date & "'"