I am trying to build sql string in c# for oracle db to select records by date. Here is the code:

mySelectQuery += String.Format("SELECT * from Training.trschedules_view where STARTDATE >= {0}",sdate.ToString("MM:dd:yyyy"));
sdate is C# DateTime field.
STARTDATE is Oracle DATE column

It returns 0 row with error: inconsistent data types: ORA-00933: SQL command not properly ended

I also tried another way to build date string from date fields:

int month = calDateFrom.SelectedDate.Month;
int day = calDateFrom.SelectedDate.Day;
int year = calDateFrom.SelectedDate.Year;
string sdate = month+"/"+day+"/"+year;
month = calDateTo.SelectedDate.Month;
day = calDateTo.SelectedDate.Day;
year = calDateTo.SelectedDate.Year;
string edate = month+"/"+day+"/"+year;

Then build sql string:
mySelectQuery = "SELECT * from Training.trschedules_view where STARTDATE >= " + sdate + " and ENDDATE <= " + edate;

Now I get error:
ORA-00932: inconsistent datatypes

Can someone help?

Hello vhrao
Maybe you lose a ";".
The sql statement witch be able to executed must end with ";".
So the sql statement is executed.

vhrao,

Unlike MSSQLServer Oracle requires that the format of the date be exactly what it is expecting. This Oracle date format is configurable both for the database and for the session. You may not be able to configure the db format but if you can query the db with a select statment that returns a date (without formating it) you can probably see what the default date format is and match that in your string.Format() statement. Otherwise there is a command to set the format the way you want it, but I don't remember the command. You might try an Oracle forum.

good luck

What you don't know can hurt you!

There are two possibilities to solve this:

1. Use the TO_DATE function (not preferred method)
... WHERE START_DATE >= TO_DATE({0}, 'YYYYMMDD')", sdate.ToString("yyyyMMdd");

2. Use an OracleCommand object and add Parameters to it. By setting the parameter type to datetime, you are sure that the correct format is used.

On my blog, I created an entry which also uses Parameters and explains the advantages: http://geertverhoeven.blogspot.com/2...-by-using.html

The same works for Oracle but then you need to use the classes from System.Data.Oracle or the Oracle specific classes in Oracle.DataAccess.Client (http://download-east.oracle.com/docs...meterClass.htm). The last one is optimized but need to be installed separately.

Greetz,

Geert

http://geertverhoeven.blogspot.com

Hi Geert,
can you throw some light why it may not be the prefered method.

navdeep,

Oracle reason:
Although both give the same result, with the to_date function you have some overhead since you are converting to a string type which you don't have if you can compare date to date.

.NET reason:
Using parameters give you the following benefits:
- type check at compile time
- better protection against sql injection

Hope this helps.

Geert

http://geertverhoeven.blogspot.com

Yes I agree with .NET reason but as far as oracle goes converting string -> date would really have an overhead issue ?

If I want use a query on SqlPlus what other options I have ?

Sorry, you are right the overhead is nihil since it is only done once for the parameter. Wasn't thinking correctly. It would be a bad desision to cast the tables datetime to a string since that would be more overhead.

The only reason why to choose for the parameters solution is because of the .NET reasons mentioned before.

Ignoring .net reasons,
If in my query my field is date type and I am changing my date string (R.H.S) with to_date() will be more appropriate then changing my date type field to string type.