CODEWITHSUNDEEP
javaswing
sudipta.dey
sudipta.dey

Reputation: 178

To store a date in mySQL as dd/MM/yyyy format through Netbeans, but mySQL generally takes yyyy/MM/dd

I entered the following codes. my goal is to store a date in mySQL as dd/MM/yyyy format. But mySQL generally takes yyyy/MM/dd.

    String d=(((JTextField)jDateChooser1.getDateEditor().getUiComponent()).getText()); 
    Statement stmt = db.conn.createStatement(); 
    String sql = "insert into diag.current " +     
                 "values'"+col1+"','"+col2+"','"+java.sql.Date.valueOf(d)+"','"+col4+"')";    
    stmt.executeUpdate(sql);

I right clicked on the jDateChooser1 and clicked on Customized code, and entered :

jDateChooser1= new com.toedter.calendar.JDateChooser("dd/MM/yyyy","##/##/####",'_');

Now please let me know where i am making the mistake. I will be grateful if someone solves this. Thanks in Advance. I hope i portrayed clearly...

Upvotes: 0

Views: 11806

Answers (3)

kavitha
kavitha

Reputation: 1

dateChooserDialog1.showDialog(this.getFrame());
Calendar d=dateChooserDialog1.getSelectedDate();

SimpleDateFormat sdf=new SimpleDateFormat("DD-MM-YYYY");
String s=d.toString();
String dd=s;
String mm=s;
String yyyy=s;
int i=dd.lastIndexOf("DAY_OF_MONTH");
dd=dd.substring(i+13,i+16);
dd=dd.substring(0,dd.lastIndexOf(","));

int j=mm.indexOf("MONTH");
mm=mm.substring(j+6,j+9);
mm=mm.substring(0,mm.lastIndexOf(","));
j=Integer.parseInt(mm);
mm=String.valueOf(j+1);

int z=yyyy.indexOf("YEAR");
yyyy=yyyy.substring(z+5,z+10);
yyyy=yyyy.substring(0,yyyy.lastIndexOf(","));

//s.substring(busyIconIndex, busyIconIndex);

String dob=dd+"/"+mm+"/"+yyyy;
DateField.setText(dd+"/"+mm+"/"+yyyy);

Upvotes: 0

Joop Eggen
Joop Eggen

Reputation: 109567

Should be something, like the following. Better list the column names, for future changes to the table, and for readability. A ( got lost in your code. Using a prepared statement escapes strings and prevents SQL injection. For dates, integers and such it is also beneficial.

java.sql.Date date = new java.sql.Date(SimpleDateFormat("dd/MM/yyyy").parse(d).getTime());

PreparedStatement stmt = db.conn.createPreparedStatement(); 
String sql = "INSERT INTO diag.current (COL1, COL2, COL3, COL4) VALUES(?, ?, ?, ?)";
PreparedStatement stmt = db.conn.createPreparedStatement();`
stmt.setString(1, col1);
stmt.setString(2, col2);
stmt.setDate(3, date);
stmt.setString(4, col4);
stmt.executeUpdate();
stmt.close();

I have edited my program using your help as shown below:

java.sql.Date date = new java.sql.Date(SimpleDateFormatter("dd/MM/yyyy").parse(d).getTime());
PreparedStatement stmt = db.conn.prepareStatement(); 
String sql = "INSERT INTO diag.current (name, patientID, address, sex, phone, vip, email, purpose, history, tests, doc, charges, status, dob, nextapp) " +     
"VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";    
PreparedStatement stmt = db.conn.createPreparedStatement(); 
stmt.setString(1, name);
stmt.setString(2, id);
stmt.setString(3, add);
stmt.setString(4, sex);
stmt.setString(5, ph);
stmt.setString(6, vip);
stmt.setString(7, mail);
stmt.setString(8, pur);
stmt.setString(9, phis);
stmt.setString(10, tests);
stmt.setString(11, dc);
stmt.setInt(12, total);
stmt.setString(13, status);
stmt.setDate(14, date);
stmt.setDate(15, date);
stmt.executeUpdate();
stmt.close();

Q: But SimpleDateFormatter and createPreparedStatement is not supporting, which import I should use?

A: Typos, should have been

java.text.SimpleDateFormat
con.prepareStatement

Upvotes: 2

Gilbert Le Blanc
Gilbert Le Blanc

Reputation: 51515

From the MySQL Manual - 11.3.1. The DATE, DATETIME, and TIMESTAMP Types

The DATE type is used for values with a date part but no time part. MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format. The supported range is '1000-01-01' to '9999-12-31'.

The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.) By default, the current time zone for each connection is the server's time.

MySQL permits a “relaxed” format for values specified as strings, in which any punctuation character may be used as the delimiter between date parts or time parts. For example YYYY/MM/DD is acceptable as a DATE.

So it appears that you have to create a text date in YYYY-MM-DD format to insert a date into MySQL. When you retrieve a DATE field, you get YYY-MM-DD.

The PreparedStatement is supposed to do this conversion for you from a java.sql.Date when you use the setDate method.

Upvotes: 0

Related Questions