Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > C# and C > C# 1.0 > C#
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old July 19th, 2007, 03:37 PM
Authorized User
 
Join Date: Oct 2004
Location: , , .
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

Reply With Quote
  #2 (permalink)  
Old July 19th, 2007, 04:31 PM
Authorized User
 
Join Date: Oct 2004
Location: , , .
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?

Reply With Quote
  #3 (permalink)  
Old July 23rd, 2007, 04:04 AM
Registered User
 
Join Date: Jul 2007
Location: beijing, beijing, China.
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.

Reply With Quote
  #4 (permalink)  
Old July 23rd, 2007, 10:36 AM
Authorized User
 
Join Date: Nov 2006
Location: Valparaiso, IN, USA.
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!
Reply With Quote
  #5 (permalink)  
Old July 25th, 2007, 12:20 AM
Authorized User
 
Join Date: Sep 2004
Location: Edegem, Antwerpen, Belgium.
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
Reply With Quote
  #6 (permalink)  
Old July 25th, 2007, 03:17 AM
Authorized User
 
Join Date: Apr 2007
Location: , , .
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.

Reply With Quote
  #7 (permalink)  
Old July 25th, 2007, 04:11 AM
Authorized User
 
Join Date: Sep 2004
Location: Edegem, Antwerpen, Belgium.
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
Reply With Quote
  #8 (permalink)  
Old July 25th, 2007, 04:20 AM
Authorized User
 
Join Date: Apr 2007
Location: , , .
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 ?
Reply With Quote
  #9 (permalink)  
Old July 25th, 2007, 06:15 AM
Authorized User
 
Join Date: Sep 2004
Location: Edegem, Antwerpen, Belgium.
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.
Reply With Quote
  #10 (permalink)  
Old July 25th, 2007, 06:24 AM
Authorized User
 
Join Date: Apr 2007
Location: , , .
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.

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 12:18 AM.


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