Insert date in Oracle table

Question

I managed to connect to the Oracle database for express edition. I created a table :

CREATE TABLE out_patient 
( 
     patient_id    NUMBER(8) PRIMARY KEY, 
     first_name    VARCHAR2(20), 
     last_name     VARCHAR2(20), 
     gender        VARCHAR2(10), 
     mobile_number NUMBER(12), 
     address       VARCHAR2(20), 
     date_of_birth DATE, 
     date_of_entry DATE 
);

Now I have a form to fill up all these details but, I'm getting an sqlexception:

ORA-01861:literals does not match format string

Can you tell what's causing it. Am I entering data in wrong format? I guess it might be due to 2 dates Date_Of_Birth and Date_Of_Entry. In my JDBC code I'm doing this:

String t1=text_dob.getText(); 
String t2=text_doe.getText();

and then,

st.setStrin(7,t1); 
st.setString(8,t2);

Please let me know what I'm doing wrong.

Leos Literak · Answer 1 · 2014-03-29T08:55:04.957

0

You need to call setDate() instead of setString on PreparedStatement:

st.setDate(7, new java.sql.Date(System.currentTimeMillis())); 
st.setDate(8, new java.sql.Date(System.currentTimeMillis()));
st.setTimestamp(1, new Timestamp(System.currentTimeMillis()));

You problem happens because your DDL column is set to Date so Oracle expects Date value but you are sending VARCHAR value.

Timestamp: see types mapping

java.sql.Date for SQL DATE information. The hour, minute, second, and millisecond fields of the java.util.Date base class should be set to zero. If the number of milliseconds supplied to the java.sql.Date constructor is negative, the driver will compute the date as the number of milliseconds before January 1, 1970. Otherwise, the date is computed as the specified number of milliseconds after January 1, 1970.

java.sql.Time for SQL TIME information. The year, month, and day fields of the java.util.Date base class are set to 1970, January, and 1. This is the "zero" date in the Java epoch.

java.sql.Timestamp for SQL TIMESTAMP information. This class extends java.util.Date by adding a nanoseconds field.

edited Mar 29 '14 at 08:55

answered Mar 29 '14 at 06:43

Leos Literak

8,805
19
81
156

Hey! i searched over some of other posts here and found out '02-JAN-2013' works well and i entered date in this format ,it worked fine.i'll try the setDate() as well. – user3367768 Mar 29 '14 at 06:47
can you please tell me how to insert time as well.columns type is Time.by default which format ?or any setTime(). – user3367768 Mar 29 '14 at 06:48
You need to pass java.sql.TimeStamp in Java – Leos Literak Mar 29 '14 at 06:49
It is there, I updated my answer. You have to use setTimestamp(int, Timestamp) method – Leos Literak Mar 29 '14 at 08:42

score 0 · Answer 2 · edited Mar 29 '14 at 08:52

0

This assumes your date entered in the GUI field is in the "dd/MM/yyyy" format. Adjust the pattern in SimpleDateFormat accordingly if it is different..

SimpleDateFormat sdf = new SimpleDateFomat("dd/MM/yyyy");
st.setDate(7,new java.sql.Date(sdf.parse(text_dob.getText()).getTime()));
st.setDate(8,new java.sql.Date(sdf.parse(text_doe.getText()).getTime()));

In case your GUI field contains date and time like "31/01/2014 23:59:59", the corresponding pattern in SimpleDateFormat is "dd/MM/yyyy HH:mm:ss". You can read the SimpleDateFormat docs for full pattern format.

http://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html

edited Mar 29 '14 at 08:52

Leos Literak

8,805
19
81
156

answered Mar 29 '14 at 06:50

anonymous

1,317
8
7

Modified answer so to call getTime() on java.util.Date and construct a new java.sql.Date. – anonymous Mar 29 '14 at 07:08
yeah!! i was getting an erro.but why getTime() ??is it necessary? – user3367768 Mar 29 '14 at 07:10
@user3367768 calling getTime() on java.util.Date to get the milliseconds since epoch. Then use this to construct the java.sql.Date – anonymous Mar 29 '14 at 07:12
can you tell me the same conversions for TIMESTAMP ,please. – user3367768 Mar 29 '14 at 07:12
hey tell me how to convert time into TIMESTAMP for storing in database.thanks – user3367768 Mar 29 '14 at 07:21
@user3367768 java.sql.Date itself IS a timestamp. It contains date and time information. Your Oracle Date type also contains date and time information. – anonymous Mar 29 '14 at 08:05
Btw did you try your timestamp proposal? I am sure that setDate() will not pass time part of date. – Leos Literak Mar 29 '14 at 08:52
@LeosLiterak Your question is valid and I have not tried it. But I think I have used I successfully in the past. The Oracle<->JDBC mapping says it is OK to use. http://docs.oracle.com/cd/F49540_01/DOC/java.815/a64685/basic3.htm – anonymous Mar 29 '14 at 09:52
I know from my past projects that it does not work and you must use Timestamp. – Leos Literak Mar 29 '14 at 10:37
@LeosLiterak I guess it very much depends on the database and the driver used. I use Oracle and the Oracle Thin JDBC driver. – anonymous Mar 29 '14 at 10:43
Pity I do not have oracle db, I would try that. – Leos Literak Mar 29 '14 at 10:45
"The JDBC DATE type represents a date consisting of day, month, and year." - e.g. if you call setDate(TimeStamp), the time is discarded. – Leos Literak Mar 29 '14 at 10:47

Insert date in Oracle table

2 Answers2