Wrox Programmer Forums
Go Back   Wrox Programmer Forums > C# and C > C# 1.0 > C#
|
C# Programming questions specific to the Microsoft C# language. See also the forum Beginning Visual C# to discuss that specific Wrox book and code.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the C# section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 19th, 2007, 03:37 PM
Authorized User
 
Join Date: Oct 2004
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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

 
Old July 19th, 2007, 04:31 PM
Authorized User
 
Join Date: Oct 2004
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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?

 
Old July 23rd, 2007, 04:04 AM
Registered User
 
Join Date: Jul 2007
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to asdf4525qd
Default

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

 
Old July 23rd, 2007, 10:36 AM
Authorized User
 
Join Date: Nov 2006
Posts: 93
Thanks: 0
Thanked 1 Time in 1 Post
Default

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!
 
Old July 25th, 2007, 12:20 AM
Authorized User
 
Join Date: Sep 2004
Posts: 40
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old July 25th, 2007, 03:17 AM
Authorized User
 
Join Date: Apr 2007
Posts: 32
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

 
Old July 25th, 2007, 04:11 AM
Authorized User
 
Join Date: Sep 2004
Posts: 40
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old July 25th, 2007, 04:20 AM
Authorized User
 
Join Date: Apr 2007
Posts: 32
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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 ?
 
Old July 25th, 2007, 06:15 AM
Authorized User
 
Join Date: Sep 2004
Posts: 40
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
 
Old July 25th, 2007, 06:24 AM
Authorized User
 
Join Date: Apr 2007
Posts: 32
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.






Similar Threads
Thread Thread Starter Forum Replies Last Post
Oracle connection string asmaa ASP.NET 2.0 Professional 0 July 17th, 2007 05:42 AM
oracle connection string pankaj_daga Oracle 2 July 12th, 2004 03:43 PM
oracle Date Problem haahoou Oracle 0 January 19th, 2004 05:43 AM
DTS Import ( Date string to Date field) gfowajuh SQL Server 2000 1 September 30th, 2003 06:28 AM
Convert String Date to Date for a SQL Query tdaustin Classic ASP Basics 4 July 7th, 2003 06:01 PM





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