how can it be that this SQL Query work from Oracle SQL Developer but not from code?

i have the following SQL query that works fine (returns multiple rows) when i run it from Oracle's SQL developer tool:

select * from  [schema].[table] where resource_unique_name in ('abc123') and (slice_date >= '01-Nov-10') and (slice_date < '01-Dec-10')

but somehow this code below returns 0 records (no errors just, 0 records):

 OracleDataReader reader = new OracleDataReader();
 const string CONNECTION_STRING = [myConnetionString];

 connection.ConnectionString = CONNECTION_STRING;
 connection.Open();

 OracleCommand command = connection.CreateCommand();
 command.CommandText = [same SQL as above];

 OracleDataReader reader = command.ExecuteReader();

 int rowCount = 0;
 while (reader.Read())
 {
      rowCount++;
开发者_StackOverflow社区 }
 Console.Write(rowCount);

the connection open's fine because if i hard code a different SQL query i do get results.

is there any reason a query would work fine in SQL query tool but not from my C# code ??

UPDATED NOTE: in my code, when i remove the:

and (slice_date < '01-Dec-10')

it seems to work fine ( i get data back). Any clue whats wrong with the above criteria ??

---

I will answer the last part in your update.

'01-Dec-10' is a string. It is not a date. It has nothing to do with dates. Not until it gets converted into a date. There are (to my knowledge) three ways of doing that.

1. Use TO_DATE()
2. Specify a date literal as date '2010-12-01', which is always iso date YYYY-MM-DD
3. Rely on implicit date conversion using the NLS settings (which you are doing)

If your NLS settings is 'DD-MON-RR', the string '01-Dec-10' would get converted into 2010-12-01 (the 1st of december, 2010). If, on the other hand, your settings are 'RR-MON-DD', it would be converted into '2001-12-10' (the 10:th of december, 2001).

I don't know your settings, and since you don't either, you should do the right thing, and that is to always explicitly use TO_DATE() or date literals.

---

Use ANSI date literals rather than just strings for the date values:

select * from [schema].[table]
where resource_unique_name in ('abc123') 
and slice_date >= date '2010-11-01' 
and slice_date < date '2010-12-01'