java 通过Netbeans将mySQL中的日期存储为dd/MM/yyyy格式，但是mySQL一般采用yyyy/MM/dd
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17808875/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
To store a date in mySQL as dd/MM/yyyy format through Netbeans, but mySQL generally takes yyyy/MM/dd
提问by sudipta.dey
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.
我输入了以下代码。我的目标是在 mySQL 中以 dd/MM/yyyy 格式存储日期。但是mySQL一般需要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 并单击自定义代码，然后输入：
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...
现在请让我知道我在哪里犯了错误。如果有人解决这个问题，我将不胜感激。提前致谢。我希望我描绘的清楚...
回答by Joop Eggen
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.
应该是这样的，如下所示。最好列出列名，以便将来更改表并提高可读性。A(迷失在您的代码中。使用准备好的语句可以转义字符串并防止 SQL 注入。对于日期、整数等，这也是有益的。
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 SimpleDateFormatterand createPreparedStatementis not supporting, which import I should use?
问：但是SimpleDateFormatter和createPreparedStatement不配套，哪些导入我应该使用？
A: Typos, should have been
A：错别字，应该是
java.text.SimpleDateFormat
con.prepareStatement
回答by Gilbert Le Blanc
From the MySQL Manual - 11.3.1. The DATE, DATETIME, and TIMESTAMP Types
来自 MySQL 手册 - 11.3.1。DATE、DATETIME 和 TIMESTAMP 类型
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.
DATE 类型用于具有日期部分但没有时间部分的值。MySQL 以 'YYYY-MM-DD' 格式检索和显示 DATE 值。支持的范围是“1000-01-01”到“9999-12-31”。
DATETIME 类型用于同时包含日期和时间部分的值。MySQL 以 'YYYY-MM-DD HH:MM:SS' 格式检索和显示 DATETIME 值。支持的范围是“1000-01-01 00:00:00”到“9999-12-31 23:59:59”。
TIMESTAMP 数据类型用于同时包含日期和时间部分的值。TIMESTAMP 的范围是 '1970-01-01 00:00:01' UTC 到 '2038-01-19 03:14:07' UTC。
MySQL 将 TIMESTAMP 值从当前时区转换为 UTC 进行存储，然后从 UTC 转换回当前时区以进行检索。（这不会发生在其他类型，例如 DATETIME。）默认情况下，每个连接的当前时区是服务器的时间。
MySQL 允许指定为字符串的值的“宽松”格式，其中任何标点字符都可以用作日期部分或时间部分之间的分隔符。例如 YYYY/MM/DD 作为 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.
因此，您似乎必须以 YYYY-MM-DD 格式创建文本日期才能将日期插入 MySQL。当您检索 DATE 字段时，您会得到 YYY-MM-DD。
The PreparedStatement is supposed to do this conversion for you from a java.sql.Date when you use the setDate method.
当您使用 setDate 方法时， PreparedStatement 应该为您从 java.sql.Date 执行此转换。
回答by kavitha
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);
