?
Solved

Passing date from c# to Oracle

Posted on 2012-08-29
15
Medium Priority
?
4,888 Views
Last Modified: 2012-10-04
Hi,

I have tried many ways to insert a date into oracle database from a query being run from c# .

but not able to.

please help.

A screen shot is also attached.
code.png
0
Comment
Question by:Dinesh Kumar
  • 4
  • 4
  • 2
  • +3
14 Comments
 
LVL 25

Expert Comment

by:lwadwell
ID: 38344480
Expecting Oracle to implicitly convert a string into a date is not always safe.  Having Oracle explicitly convert a string into a data is the way to go.  Try:
...DATE_OF_TI_REMARKS=to_date('" + Date + "','DDMMMYYYY HH24:MI:SS') where ...
0
 
LVL 23

Assisted Solution

by:OP_Zaharin
OP_Zaharin earned 1000 total points
ID: 38344488
- in your update statement, use Oracle's SYSDATE to get current date & time and then format it using to_char() function to format it to your desired format. however if DATE_OF_TI_REMARKS  is a date datatype, you are restricted to Oracle datetime format. refer to : http://ss64.com/ora/syntax-fmt.html

eg: String Query= @"update ......... DATE_OF_TI_REMARKS = to_char(SYSDATE,'ddmmyyyy hh:mi:ss') where Receipt_no='" + ReceiptNo + "'";
0
 
LVL 25

Expert Comment

by:lwadwell
ID: 38344490
For future reference - some links to more into on Oracle's TO_DATE() function.
http://www.techonthenet.com/oracle/functions/to_date.php
or
http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions183.htm
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Dinesh Kumar
ID: 38344896
don't you think that  I should know the server date time format for oracle insert first?
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 38344921
actually, you did 50% of the job. in your c# code, you formatted the date into a string with a given format.
in the SQL, you then need to reconvert it back to a date (using TO_DATE() as suggested above)

eg: String Query= @"update ......... DATE_OF_TI_REMARKS = TO_DATE('" + Date + "' ,'ddmmyyyy hh:mi:ss') where Receipt_no='" + ReceiptNo + "'"; 

Open in new window


read also this article:
http://www.experts-exchange.com/Database/Miscellaneous/A_1499-DATE-and-TIME-don%27t-be-scared-and-do-it-right-the-first-time.html

alternative would be to use parameters as you did already of the other values.

finally, if you really only needed the "now":

eg: String Query= @"update ......... DATE_OF_TI_REMARKS = SYSDATE where Receipt_no='" + ReceiptNo + "'"; 

Open in new window

and hence no need to pass the "date" value from the client to the server
0
 
LVL 25

Expert Comment

by:lwadwell
ID: 38344923
Oracle actually stores dates internally as a decimal number where the integer portion is the number of days since the epoch (1/1/1970) and the fractional portion is the fraction of a day.  You cannot specify dates to Oracle in that format.  The TO_DATE() function supports a wide variety of formats that can be converted into an internal date value.  Have a look at the links I provided ... they detail the actual format options.
The TO_DATE() function is the Oracle method to 'cast' strings into datetime datatypes.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38345164
The best way is to use bind variables to help protect against SQL injection and help SQL parsing.

Delcare a native date variable:
http://www.oracle.com/technetwork/issue-archive/2005/05-sep/o55odpnet-101704.html

Then you might not have to worry about to_char conversions.

>>don't you think that  I should know the server date time format for oracle insert first?

Mentioned above, Oracle uses sysdate.  Not sure what you are using it for based on the code you posted.
0
 
LVL 35

Expert Comment

by:johnsone
ID: 38345382
@lwadwell

That is not how dates are internally stored.  They are stored in a 7 byte notation.  The best explanation of how they are internally stored (that I have found) is here:

http://docs.oracle.com/cd/E11882_01/appdev.112/e10825/pc_04dat.htm#sthref442

They are not stored as a number of days since the epoch.  You cannot get the significant date range the Oracle supports using that.
0
 
LVL 25

Expert Comment

by:lwadwell
ID: 38345552
thanks @johnstone ... I will strive to remember that in future.  I am struggling to remember where I picked my idea from - perhaps I have it confused with something else I have worked with in my past ... it has me thinking now.
0
 

Accepted Solution

by:
Dinesh Kumar earned 0 total points
ID: 38375488
Thank you but I would like to add:

 TO_DATE('" + Date + "' ,'ddmmyyyy hh:mi:ss')

this will not work if you supply string date as 28/08/2012  (dd/mm/yyyy)

for it I need to specify:

 TO_DATE('" + Date + "' ,'dd/mm/yyyy hh:mi:ss')
0
 

Author Comment

by:Dinesh Kumar
ID: 38382610
Sorry OP_Zaharin's first comment was also part of the solution.
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 38382862
- you can ask for assistance on the from the site moderator by clicking the "Request Attention icon" to change the accepted answer.
0
 

Author Comment

by:Dinesh Kumar
ID: 38455191
I've requested that this question be closed as follows:

Accepted answer: 450 points for angelIII's comment #a38344921
Assisted answer: 0 points for meetDinesh's comment #a38375488

for the following reason:

Thanks
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38455192
meetDinesh,

I unaccepted this because in http:#a38382610 you stated that OP_Zaharin should get some of the points.

You have again failed to give him any.  Therefore I'll object to this closing and hopefully you can close this properly.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses
Course of the Month13 days, 20 hours left to enroll

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question