Quick JDBC/Oracle Question

[AsifTheManRahman]

Does anyone know how dates are stored in Oracle? I'm trying to get today's date and insert it into my database using SQL statements in my JDBC. I'm currently using java.util.date, but am getting an SQL exception when I try to execute my prepared statement. I was wondering whether this could happen because I have a couple of columns of type date of length 7 (Oracle default) but I am probably sending in the wrong format to the database with the JDBC.

Here's the resulting SQL: "INSERT INTO BUNDLE (ESELLINGID,CREATE_DATE,CREATE_BY,LST_UPD_DATE,LST _UPD_USR,EN,FR_CA,AR) VALUES ('eselling.test1','Fri Oct 14 12:10:05 EDT 2005','Fri Oct 14 12:10:05 EDT 2005','Fri Oct 14 12:10:05 EDT 2005','Fri Oct 14 12:10:05 EDT 2005','Fri Oct 14 12:10:05 EDT 2005','Fri Oct 14 12:10:05 EDT 2005','Fri Oct 14 12:10:05 EDT 2005')"

looks ok to me.

oh and another thing: if i want to get a date using a java method and insert that into a varchar field in the back end, do I have to convert the date to a string? or can Oracle still store it?

Edited on, October 14, 2005, 4:17 PM GMT, by AsifTheManRahman.

[AsifTheManRahman]

ok i don't need it anymore, but would sure like to know the answer if anyone ever comes across anything like that.

[Mahmood]

The oracle date is dd-mmm-yyyy format.

You are trying to insert a date objects toString value. To do that.. use this instead

INSERT INTO BUNDLE
(ESELLINGID,CREATE_DATE,CREATE_BY,LST_UPD_DATE,LST _UPD_USR,EN,FR_CA,AR)
VALUES
('eselling.test1',
to_date(replace('Fri Oct 14 12:10:05 EDT 2005','EDT'),'Dy Mon dd hh24:mi s yyyy'),
.............

Or do it the right way....

java.util.Date dt = new java.util.Date();
java.text.DateFormat format = new java.text.SimpleDateFormat( "dd-MMM-yyyy" );
String myDate = format.format(dt);

myDate is now the string 14-OCT-2005
Now use this myDate to insert in your query...to insert, list this value as..

INSERT INTO BUNDLE
(ESELLINGID,CREATE_DATE,CREATE_BY,LST_UPD_DATE,LST _UPD_USR,EN,FR_CA,AR)
VALUES
('eselling.test1',
TO_DATYE('14-OCT-2005', 'dd-mon-yyyy'),
.............

[Rubu]

SQL accept date format: yyyy-mm-dd

But java data format is, mm-dd-yyyy by default. I believe that is what causing the problem. you can change the date format for java, and fix the date format to yyyy-mm-dd and i think everything will be fine.

here is some code, that might help:

use these imports:
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;

then, the code goes something like....
Date dst = new Date();
SimpleDateFormat yourDate = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
st = yourDate.format(dst);

Edited on, October 14, 2005, 6:56 PM GMT, by Rubu.

[AsifTheManRahman]

originally posted by Mahmood

The oracle date is dd-mmm-yyyy format.

You are trying to insert a date objects toString value. To do that.. use this instead

INSERT INTO BUNDLE
(ESELLINGID,CREATE_DATE,CREATE_BY,LST_UPD_DATE,LST _UPD_USR,EN,FR_CA,AR)
VALUES
('eselling.test1',
to_date(replace('Fri Oct 14 12:10:05 EDT 2005','EDT'),'Dy Mon dd hh24:mi s yyyy'),
.............

Or do it the right way....

java.util.Date dt = new java.util.Date();
java.text.DateFormat format = new java.text.SimpleDateFormat( "dd-MMM-yyyy" );
String myDate = format.format(dt);

myDate is now the string 14-OCT-2005
Now use this myDate to insert in your query...to insert, list this value as..

INSERT INTO BUNDLE
(ESELLINGID,CREATE_DATE,CREATE_BY,LST_UPD_DATE,LST _UPD_USR,EN,FR_CA,AR)
VALUES
('eselling.test1',
TO_DATYE('14-OCT-2005', 'dd-mon-yyyy'),

no, actually, the SQL that i sent you was not done manually, but rather the result of passing in variables into the preparedStatement. I wasn't typing in anything manually, and neither was I doing a toString().

instead, i was somewhat following your way. this is what i was doing:

java.util.Date dt = new java.util.Date();

and then passing in a string of dt's into the prepared statement.

i guess the thing that i was doing wrong is that i wasnt formatting the dates, i.e. i wasnt using any DateFormat.

i'll try it out. thanks.




Edited on, October 14, 2005, 7:06 PM GMT, by AsifTheManRahman.

[AsifTheManRahman]

and thanks rubu.

[Mahmood]

Originally posted by AsifTheManRahmanno, actually, the SQL that i sent you was not done manually, but rather the result of passing in variables into the preparedStatement. I wasn't typing in anything manually, and neither was I doing a toString().

instead, i was somewhat following your way. this is what i was doing:

java.util.Date dt = new java.util.Date();

and then passing in a string of dt's into the prepared statement.

i guess the thing that i was doing wrong is that i wasnt formatting the dates, i.e. i wasnt using any DateFormat.

i'll try it out. thanks.




Edited on, October 14, 2005, 7:06 PM GMT, by AsifTheManRahman.

In that case... here is whats best.

Use your java date object to be java.sql.Date.

Then in your prepare statement, just pass the date object with setDate and all done.

[AsifTheManRahman]

yes i was thinking about java.sql.Date, but wasnt sure. thanks.