I have SQL that is returning 24 rows in SQLPlus however when I run the same query in VS2010 using Provider=OraOLEDB.Oracle its doesn't return any rows. I am using a LIKE operator with to_date. Any suggestions? Is it the provider I am using the problem?
select level from testTable where dTime LIKE to_date('3/27/2013','MM/DD/YYYY') and station = '019654'orderby dTime
Dim dsFred = New DataSet
conn = New OleDbConnection("Provider=OraOLEDB.Oracle;User ID=test;Password=testPass;Data Source=ARC")
sql = "select level from testTable where dTime LIKE to_date('3/27/2013','MM/DD/YYYY') and station = '019654' order by dTime"
myCommand = New OleDbDataAdapter(sql, conn)
Dim fred AsInteger = dsFred.Tables("fred").Rows.Count
That's a funny Oracle feature. LIKE with a date/datetime field does not make sense, I think. But I learned that "The date column will be implicitly converted to a character string (according to nls_date_format) before the comparison" (https://forums.oracle.com/forums/thread.jspa?threadID=2132385[^]) with Oracle. Never did I expect such a nonsense. What's your requirement behind that LIKE?
And I guess that the cited text shows the reason for the failure in one situation and the success in another: the nls_date_format differs.
Last Visit: 31-Dec-99 18:00 Last Update: 5-Jul-15 0:37