p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   C# (http://p2p.wrox.com/forumdisplay.php?f=22)
-   -   Oracle Date sql string in C# (http://p2p.wrox.com/showthread.php?t=60289)

vhrao July 19th, 2007 03:37 PM

Oracle Date sql string in C#
 
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


vhrao July 19th, 2007 04:31 PM

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?


asdf4525qd July 23rd, 2007 04:04 AM

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


David_0223 July 23rd, 2007 10:36 AM

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!

GeertVerhoeven July 25th, 2007 12:20 AM

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

navdeep July 25th, 2007 03:17 AM

Quote:

quote:Originally posted by GeertVerhoeven


Use the TO_DATE function (not preferred method)

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


GeertVerhoeven July 25th, 2007 04:11 AM

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

navdeep July 25th, 2007 04:20 AM

Quote:

quote:Originally posted by GeertVerhoeven
 navdeep,

Oracle reason:

.NET reason:

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 ?

GeertVerhoeven July 25th, 2007 06:15 AM

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.

navdeep July 25th, 2007 06:24 AM

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.



All times are GMT -4. The time now is 03:09 AM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.