I'm sorry, I know this must come-up a lot, but I cannot find the magic search words or something. Can anyone show me a good method for converting a DateTime type from Now to Oracle's "interesting" and inflexible one (eg: '10-OCT-07') so I can insert it without issues?

thanks

If you are using a parameter of type Date, you don't need to convert what is returned by Now().

In case you want to send the date as a string, which you shouldn't really have to, you can use Now().ToString("dd-MMM-yy") 

ToString has a special overload for Dates that takes a string format as a parameter

What I am doing is an insert of type DateTime into an Oracle field of type Date

There is no string conversion, and Now() by itself fails due to format (Invalid Month Error)

So I don't think these really apply unless I do some sort of Date-to-string-then-back-to-Date conversion, since the standard format overload will produce a string...correct?

Can you post your insert statement or  the method you are using for the insert?

Here's the basics of it:

This causes invalid month error (while doing the actual insert into Oracle):

DateTime cFiled;

cFiled = System.DateTime.Now();

sqlInsert = "INSERT INTO tbl(OracleDate) VALUES ('" + cFiled + "')";

If I do this it's going to cause implicit conversion errors:

DateTime cFiled;

cFiled = System.DateTime.Now.ToString("dd-MMM-yy");

sqlInsert = "INSERT INTO tbl(OracleDate) VALUES ('" + cFiled + "')";

... Execution Code...

It seems I could assemble something using Now() values, but I'd need to be able to get the month name for parsing, which I thought was available,
but apparently not the way it used to be...

It seems to me that there has got to be a simpler way in general, while mataining the DateTime datatype...

MonkeyHead:

Here's the basics of it:

This causes invalid month error (while doing the actual insert into Oracle):

DateTime cFiled;

cFiled = System.DateTime.Now();

sqlInsert = "INSERT INTO tbl(OracleDate) VALUES ('" + cFiled + "')";

 

If I do this it's going to cause implicit conversion errors:

 

DateTime cFiled;

cFiled = System.DateTime.Now.ToString("dd-MMM-yy");

sqlInsert = "INSERT INTO tbl(OracleDate) VALUES ('" + cFiled + "')"

It appears to me that you are actually treating it as a string in your query in either case...., Is that a type-o maybe?

sqlInsert = "INSERT INTO tbl(OracleDate) VALUES ('" + cFiled + "')";

You want it without quotes there or you get the literal value "cFiled".....  (which would certainly not insert well into a Date field)

You also have parens on now "()" take those out, or you'll get a method / property usage error.

Since you are inserting Now, use the following

 sqlInsert = "INSERT INTO tbl(OracleDate) VALUES (sysdate)";

In cases where you have to insert other dates, using parameterized statements will help you avoid these issues.

OracleConnection con = new OracleConnection(conStr); 

sqlInsert = "INSERT INTO tbl(OracleDate) VALUES (:myDate)";

OracleCommand cmd = new OracleCommand(sqlInsert,con);

cmd.Parameters.Add("myDate",DateTime) .value=now(); //or any other Date

cmd.ExecuteNonQuery(); 

I actually began all this using sysdate but a req requires that more than one operation be done and if i use sysdate there will be a possibility for inconsistencies so unfortunately i'll need it operating as a date within the code.

thank you daniel yes those were mistakes but still having problems aftetr correcting them.

 i think at this point i'll just see if i can change the req to accomodate strings within the databases so i don't spend ages on this, and it looks like easy answers arent going to work.

 thanks you anyways

So I assume then that since you are storing Date by days that the issue is that an operation may occur at midnight and the same record may then have two different dates for the same data?

I would tend to default to the ToString overloads, but knowing that those will give you string values, you would still need to perform more operations to get what you want.

You can always just use Oracle's SYSDATE for the initial insert and then, to complete your operations do an ExecuteScalar to retrieve that value to complete your operations.

It's not lovely, but it's not too inefficient under the circumstances, and would ensure that you evaluate against the original date that you inserted.....

(Just-in-case: You might keep-in-mind that SYSDATE may well be inserting a full date/time into Oracle too, it's not always going to use that format regardless, so check the storage as well, you may not have nearly the problem that you think you have.....)

dcpennington2:

So I assume then that since you are storing Date by days that the issue is that an operation may occur at midnight and the same record may then have two different dates for the same data?

I would tend to default to the ToString overloads, but knowing that those will give you string values, you would still need to perform more operations to get what you want.

You can always just use Oracle's SYSDATE for the initial insert and then, to complete your operations do an ExecuteScalar to retrieve that value to complete your operations.

It's not lovely, but it's not too inefficient under the circumstances, and would ensure that you evaluate against the original date that you inserted.....

 

(Just-in-case: You might keep-in-mind that SYSDATE may well be inserting a full date/time into Oracle too, it's not always going to use that format regardless, so check the storage as well, you may not have nearly the problem that you think you have.....)

i did that once i had been authorized to do the string conversions and it did work prett7y fast. since i can store a string now i am in much better shape.

thank you both for the help

I suggest you dont waste your time in solving incompatibilities between oracle and .net. Just use Varchar2 as a parameter in Oracle SP and convert this varchar parameter to the date value using to_date function with whatever format you want. In .net use the ToString method on your DateTime variable to format it in the manner you have done in the to_date part in oracle.

Hope this helps!

Date.Now.ToString("yyyy-MMM-dd")

doesnt make sense but works.

You can also try  Date.Now.ToString("dd-MMM-yyyy")

universal function to convert any oracle date to .net date

to_char(doe,'MM-DD-YYYY HH24:MI:SS')= '" & doe.ToString("MM-dd-yyyy HH:mm:ss")

(doe is of type date) 

The format of the second parameter for oracle date function should match the format of the parameter for the .net function.

example shown below

Dim query As String

Try

query = "Update tablename set date1 ='" & Date.Now.ToString("yyyy-MMM-dd") & "',username= '" & DatabaseConnections.Username() & "' where id= " & number& " and user_id='" & username & "' and to_char(doe,'MM-DD-YYYY HH24:MI:SS')= '" & doe.ToString("MM-dd-yyyy HH:mm:ss") & "'"

Dim i As Integer

Catch ex As Exception

Finally

End Try

 A call to the above function will be like