how to get the records greater than date in the oracle database

Hi Friends,

I am sending sql db datetime field LastId paramater like this

cmd = new OracleCommand("select BUSINESS_DATE, TRANS_SEQ ,sum(UNIT_PRICE) TotalPrice,,sum(VAT_AMT) TotalTax,sum(GROSS_AMT) TotalPricewithTax from LINEITM_VIEW group by TRANS_SEQ ,BUSINESS_DATE Having BUSINESS_DATE >= TO_TIMESTAMP(:LastId,'yyyyMMddHH24missffff')", con);

cmd.CommandType = CommandType.Text;

OracleParameter param = new OracleParameter(":LastId", OracleDbType.TimeStamp, ParameterDirection.Input);

param.Value = LastID();

cmd.Parameters.Add(param);

I am not getting the correct result in the front end.

I am also sending oracle query like this

Select \"BUSINESS_DATE\",\"TRANS_SEQ\",SUM(\"VAT_AMT\") \"TotalTax\",SUM(\"UNIT_PRICE\") \"TotalPrice\",SUM(\"GROSS_AMT\") \"TotalPricewithTax\" from \"LINEITM_VIEW\" GROUP BY \"TRANS_SEQ\",\"BUSINESS_DATE\" HAVING BUSINESS_DATE >= TO_TIMESTAMP('2018-12-31 08:52:09.270', 'yyyyMMddHH24missffff')

getting errors : object cannot be cast from DBNull to other types

for this error I changed query like this

Select to_char(BUSINESS_DATE,'YYYY-MM-DD') BUSINESS_DATE,TRANS_SEQ,SUM(NVL(VAT_AMT, 0)) TotalTax,SUM(NVL(UNIT_PRICE, 0)) TotalPrice,SUM(NVL(GROSS_AMT, 0)) TotalPricewithTax from LINEITM_VIEW GROUP BY TRANS_SEQ,BUSINESS_DATE

getting error: ORA-01810: format code appears twice

missingg expression error also coming .

Please tell me how to get the oacle db records by passing sql datetime parameter.

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

prasanna p5y ago
Hi Aman Gupta,

I will test with that query.

If unitprice,Total amt fields have 0 or null value then how to use oracle query in the front end.
0
Aman Gupta5y ago
As your query string is a literal, and assuming your dates are properly stored as DATE you should use date literals: SELECT * FROM OrderArchive WHERE OrderDate <= DATE '2015-12-31' If you want to use TO_DATE (because, for example, your query value is not a literal), I suggest you to explicitly set the NLS_DATE_LANGUAGE parameter as you are using US abbreviated month names. That way, it won't break on some localized Oracle Installation: SELECT * FROM OrderArchive WHERE OrderDate <= to_date('31 Dec 2014', 'DD MON YYYY','NLS_DATE_LANGUAGE = Indian');
0