insert date in sqlite database from java

Question

My frontend is java and backend (database) is sqlite.

When I select date from JDateChooser, I want to store date and time like this:

Saturday, January 17, 2015 3:44:12 PM

So how do I do this ?

I don't know what value I should write in the dateFormatString property of jDateChooser so as to get my above formatted date and time.

I have four columns in the table. One column name is "Date" and its datatype is DATETIME.

In this column i want to store above formatted date and time.

For date and time insertion, I wrote the following code:

try
{   String sql = "insert into tbexpense (`No.`, Subject_expense, Cost, Date) values (?, ?, ?, ?)";
    pst = conn.prepareStatement(sql);
    pst.setString(1, txtNo.getText());
    pst.setString(2, txtexpense.getText());
    pst.setString(3, txtcost.getText());
    pst.setString(4, ((JTextField)jDateChooser1.getDateEditor().getUiComponent()).getText());
    pst.execute();
    JOptionPane.showMessageDialog(null, "Saved.");
    Update_table();
}
catch(Exception e)
{   JOptionPane.showMessageDialog(null, e);
}

You don't store date with a format. It is not a string. Usually, it's an internal implementation that indicates the time elapsed since a given "zero" date. You only format it when you need to display it, not when you store it. — RealSkeptic
– RealSkeptic, Commented Jan 17, 2015 at 13:31
Just store the date, storing it as a particular formatted string, will cause you all sorts of problems. — Tony Hopkinson
– Tony Hopkinson, Commented Jan 17, 2015 at 13:31
The format is YYYY-MM-DD HH:MM:SS. Read this documentation for more information. — Tom
– Tom, Commented Jan 17, 2015 at 13:39
Before storing the date in database, How to format date with name of the days of the week, month, 12-hour clock with AM or PM — user3216114
– user3216114, Commented Jan 17, 2015 at 13:48
@user3216114 For what purpose? MySQLs DateTime format won't accept that. — Tom
– Tom, Commented Jan 17, 2015 at 13:49

RealSkeptic · Accepted Answer · 2015-01-17 13:58:45Z

The proper way to store your date is to store it as a Timestamp object. A TIMESTAMP column in a database is not a string, and it doesn't have a format.

Setting date/timestamp fields using a string is bad practice as there can be ambiguities. JDBC allows you to set it directly as a java.sql.Date or a java.sql.Timestamp using PreparedStatemet's setDate() and setTimestamp() respectively.

So you get the date from your JDateChooser using getDate(), convert it into milliseconds, create a Timestamp object from it, and set it using setTimestamp.

try
{   String sql = "insert into tbexpense (`No.`, Subject_expense, Cost, Date) values (?, ?, ?, ?)";
    pst = conn.prepareStatement(sql);
    pst.setString(1, txtNo.getText());
    pst.setString(2, txtexpense.getText());
    pst.setString(3, txtcost.getText());
    pst.setTimestamp(4, new java.sql.Timestamp(jDateChooser1.getDate().getTime()) );
    pst.execute();
    JOptionPane.showMessageDialog(null, "Saved.");
    Update_table();
}
catch(Exception e)
{   JOptionPane.showMessageDialog(null, e);
}

When you need to display the date that has been stored in the database, you can format it properly using DateFormatter. At that point, and only at that point, you convert the Date or Timestamp object to a string.

Collectives™ on Stack Overflow

insert date in sqlite database from java

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related