I am having issues when comparing dates and date/time values in an Oracle 9i database. I am using LLBLGen version 1.0.2005.1.

Here is a sample C# code snippet:

whereExpression.Add(PredicateFactory.CompareValue(AdvMtgHistFieldIndex.MtgDteTim, ComparisonOperator.GreaterEqual, startDate, "History"));  

startDate is of type System.DateTime.

With this line of code present, no rows are returned. Without this date comparison code, rows ARE returned. Two of the rows have values in this DATE column of "2006-09-15 13:00:00" and "2006-09-15 12:00:00".

When I turn on tracing using the following system.diagnostics tags in web.config...

<system.diagnostics>
  <switches>
    <add name="OracleDQE" value="4" />
    <add name="SqlServerDQE" value="4" />
  </switches>
</system.diagnostics>

...the SQL generated related to this portion of the WHERE clause looks like this:

"ADV_MTG_HIST"."MTG_DTE_TIM" >= :MtgDteTim1 and the parameter looks like this: Parameter: :MtgDteTim1 : Date. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 9/15/2006 12:00:00 AM.

Does anyone know if this is the actual SQL sent to Oracle? This will not work when used in SQLPlus or other Oracle client tools I have used.

By the way, the product I maintain runs against both MS SQL Server and Oracle (thanks to the new typeconverter feature !). Unfortunately date comparisons only seem to work against MS SQL Server. Anyone know if I am doing anything wrong?

Thanks in advance!

There's a time value stored in the date, perhaps you should use a betweem query between 00:00 and 23:59?

Unfortunately I can't use a BETWEEN query because I need to see all records greater than a certain date (not in between two dates).

We are also a PowerBuilder shop, and the PowerBuilder datawindow generates the correct SQL when comparing dates (for both MS SQL Server and Oracle).

Doesn't ODP.NET help generate the correct SQL? I'm surpised this is not working. Any other input would be greatly appreciated.

Adam wrote:

Unfortunately I can't use a BETWEEN query because I need to see all records greater than a certain date (not in between two dates).

We are also a PowerBuilder shop, and the PowerBuilder datawindow generates the correct SQL when comparing dates (for both MS SQL Server and Oracle).

Doesn't ODP.NET help generate the correct SQL? I'm surpised this is not working. Any other input would be greatly appreciated.

Adam,

Did you try with: startDate.Date ?

Try to run the generated query in SQL Plus and see if it returns any records (replace the parameter :MtgDteTim1 with its value)

Adam wrote:

Unfortunately I can't use a BETWEEN query because I need to see all records greater than a certain date (not in between two dates).

Ok, but realize that if there's a time component stored in the value you're comparing with, it will use it, so if the db date starts at 1 oclock and you compare it with something which has time at 3 oclock, you won't find that date. The parameter value in the query uses 12:00AM, which is 00:00, so the value is correct (IMHO, I'm not raised with AM/PM but if I'm not mistaken, 12:00 AM is midnight).

The query you see when you enable DQE tracing is the query send to the DB. The SQL is generated by the Oracle DQE, ODP.NET doesn't generate SQL. The SQL is correct, it might not work 1:1 in sql+ because of the parameter.

When you substract a day (just for testing) do you get a value returned then?

Thanks for the responses everyone. I have to do some more testing, but I believe one of my issues is related to a registry setting named NLS_DATE_FORMAT. My date comparisons seem to be working, but I need to verify that my date/time comparisons are working as well. For example, searching for all meetings on Jan 17th, 2007 between 1:00PM and 7:00PM.

Unfortunately most of my headaches are related to the Oracle client tools:

Walaa wrote:

Try to run the generated query in SQL Plus and see if it returns any records (replace the parameter :MtgDteTim1 with its value)

If you try to run queries that compare date columns with literal date strings (like '09/16/2006') in SQL Plus or another Oracle-compatible SQL Client tool, it will not work. Oracle does not like comparing literal date string values without using the TO_DATE function.

Otis wrote:

The SQL is correct, it might not work 1:1 in sql+ because of the parameter.

I think you are right Frans. I wonder if ODP.NET successfully runs these queries by translating them into something like a parameterized or bound query. Debugging against MS SQL Server is so much nicer because I can typically just copy and paste the SQL generated by the DQE into Query Analyzer.

Otis wrote:

When you substract a day (just for testing) do you get a value returned then?

This isn't an issue because the same code runs successfully against MS SQL Server. I just wish the Oracle tools were as friendly as Query Analyzer!!

Thanks again everyone. I'll post back to this thread if I find my issue was related to something other than the NLS_DATE_FORMAT registry setting. For now I will mark this issue as resolved.