String query="select TOTAL_SUBS, ACTIVE_SUBS,NEW_SUBSCRIPTION, ONE_WEEK_RENT,TWO_WEEK_RENT,THREE_WEEK_RENT,SUBSCRIPTION ,RBT_PURCHASE,RBT_GIFT,TOTAL_CALL_PULSE ,SMS , ((NEW_SUBSCRIPTION_AMOUNT+ONE_WEEK_AMOUNT+TWO_WEEK_AMOUNT+THREE_WEEK_AMOUNT+SUBSCRIPTION_AMOUNT+TOTAL_PULSE_AMOUNT+SMS_AMOUNT)/2+(RBT_PURCHASE_AMOUNT+RBT_GIFT_AMOUNT)/4)/USD_RATE as REVENUE from daily_invoice where invoice_date like '03-12-2010%'";
I bet that the problem is difference in national settings, and the horrible where clause (where invoice_date like '03-12-2010%').
The invoice_date column is most probably a DATE type. When you compare it to a text value using the LIKE operator, it is implicitly (silently) converted to text using actual national settings. When you run the query in the database client, the national settings are probably set to a format you expect, but in the Java environment it is something different (eg. "Dec 3, 2010" or "2010/12/03"). This does not match the format you're using in the where clause, so you don't get any matching records.
You're probably trying to get all records for December 3rd, 2010. I'd expect the invoice date to not contain time component, because time is usually not present on invoices, but if you really want to be sure, strip the time part using appropriate function for your database (eg. in Oracle it would be trunc(invoice_date, 'DD')). Then compare this value to a date value specified using bind value.
(Note: it is possible to solve his issue without using binds. However, not using binds is another serious bug you definitely should fix in your code. If you don't know the details of binds, read the PreparedStatement documentation, search this forum, or look for a decent JDBC tutorial. There are many of the net.)
Always avoid using implicit conversions in SQL. They are ticking bombs in your code. Imagine your program would by accident work on your computer, but when you deploy it onto another system, or someone at a later time changes the national settings for any reason, your program all of sudden stops working.
Jan Cumps wrote:By the way, isn't the Oracle string representation of a date '03-DEC-2010' in stead of '03-12-2010'? And '31-DEZ-2010' in Germany?
I don't know, I've actually made the examples up. The implicit conversion format definitely depends on current session NLS settings, which might be redefined in a login script or on logon trigger to any non-standard, user defined format.