Mark,
Thanks for the tip, works great. But what would the Select statement look like if I wanted to filter on the datetime field with the below line?
[AND sales.order_date = TO_DATE('] + mydate + [', 'MM/DD/YYYY') ]
Thanks,
Jerry
>Try an easier way to create and test SQL:
>
>lc_custno = [123]
>lc_date = DTOT(DATE(2004, 7, 1))
>
>TEXT to lc_SQL TEXTMERGE NOSHOW PRETEXT 3
> SELECT *
> FROM MyOracle.Sales Sales
> WHERE Sales.customer = ?lc_custno
> AND Sales.OrderDate >= ?lc_date
> ORDER BY Sales.OrderDate
>ENDTEXT
>*!* get rid of line returns
>lc_SQL = STRTRAN(lc_SQL, CHR(13) + CHR(10), [ ])
>nresult = SQLEXEC(myhandle, lc_SQL, 'myresults')
>
>>I am trying to query an Oracle table. The following works fine except when I try to add a where clause for the OrderDate field.
>>
>>What would be the proper way to query this Oracle table with a where clause on the OrderDate (datetime) field?
>>
>>***This works without the OrderDate clause.
>>lc_custno = [123]
>>lc_sql = [SELECT * ] + ;
>> [FROM MyOracle.Sales Sales ] + ;
>> [WHERE Sales.customer = '] + lc_custno + [' ] + ;
>> [ORDER BY Sales.OrderDate]
>>nresult = SQLEXEC(myhandle, lc_sql, 'myresults')
>>
>>***This does not work and the nresult = -1.
>>lc_custno = [123]
>>lc_date = [07/01/2004 12:00:00 AM]
>>lc_sql = [SELECT * ] + ;
>> [FROM MyOracle.Sales Sales ] + ;
>> [WHERE Sales.customer = '] + lc_custno + [' ] + ;
>> [AND Sales.OrderDate >= '] + lc_date + [' ] + ;
>> [ORDER BY Sales.OrderDate]
>>nresult = SQLEXEC(myhandle, lc_sql, 'myresults')
>>
>>How can I filter a datetime field in a Oracle SQL call?
>>
>>Thanks,
>>
>>Jerry