facebook Twitter RSS Feed YouTube StumbleUpon

Home | Forum | Chat | Tours | Articles | Pictures | News | Tools | History | Tourism | Search

 
 


Go Back   BanglaCricket Forum > Miscellaneous > Support

Support Help & Support

Reply
 
Thread Tools Display Modes
  #1  
Old October 14, 2005, 11:16 AM
AsifTheManRahman's Avatar
AsifTheManRahman AsifTheManRahman is offline
Super Moderator
BC Editorial Team
 
Join Date: February 12, 2004
Location: Canada
Favorite Player: Ice Man, Chatter Box
Posts: 27,675
Default Quick JDBC/Oracle Question

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.
Reply With Quote
  #2  
Old October 14, 2005, 12:59 PM
AsifTheManRahman's Avatar
AsifTheManRahman AsifTheManRahman is offline
Super Moderator
BC Editorial Team
 
Join Date: February 12, 2004
Location: Canada
Favorite Player: Ice Man, Chatter Box
Posts: 27,675

ok i don't need it anymore, but would sure like to know the answer if anyone ever comes across anything like that.
Reply With Quote
  #3  
Old October 14, 2005, 01:55 PM
Mahmood's Avatar
Mahmood Mahmood is offline
Administrator
Operations & Administrations
 
Join Date: June 20, 2002
Location: Montreal, Canada
Favorite Player: Mashrafe Mortaza
Posts: 7,776

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'),
.............
Reply With Quote
  #4  
Old October 14, 2005, 01:56 PM
Rubu's Avatar
Rubu Rubu is offline
Moderator
 
Join Date: February 15, 2004
Location: Michigan
Favorite Player: Tamim Iqbal
Posts: 8,198

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.
Reply With Quote
  #5  
Old October 14, 2005, 02:00 PM
AsifTheManRahman's Avatar
AsifTheManRahman AsifTheManRahman is offline
Super Moderator
BC Editorial Team
 
Join Date: February 12, 2004
Location: Canada
Favorite Player: Ice Man, Chatter Box
Posts: 27,675

Quote:
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.
Reply With Quote
  #6  
Old October 14, 2005, 02:06 PM
AsifTheManRahman's Avatar
AsifTheManRahman AsifTheManRahman is offline
Super Moderator
BC Editorial Team
 
Join Date: February 12, 2004
Location: Canada
Favorite Player: Ice Man, Chatter Box
Posts: 27,675

and thanks rubu.
Reply With Quote
  #7  
Old October 14, 2005, 02:09 PM
Mahmood's Avatar
Mahmood Mahmood is offline
Administrator
Operations & Administrations
 
Join Date: June 20, 2002
Location: Montreal, Canada
Favorite Player: Mashrafe Mortaza
Posts: 7,776

Quote:
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.
Reply With Quote
  #8  
Old October 14, 2005, 02:13 PM
AsifTheManRahman's Avatar
AsifTheManRahman AsifTheManRahman is offline
Super Moderator
BC Editorial Team
 
Join Date: February 12, 2004
Location: Canada
Favorite Player: Ice Man, Chatter Box
Posts: 27,675

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

Bookmarks


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
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 On
HTML code is On



All times are GMT -5. The time now is 05:10 PM.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2014, vBulletin Solutions, Inc.
BanglaCricket.com
 

About Us | Contact Us | Privacy Policy | Partner Sites | Useful Links | Banners |

© BanglaCricket