input date using jdbc to hsqldb

Question

How do i get all the rows having endDate greater than current date? do i have to use setString() or setDate()? they are both not working!!! What is the correct way to do it? ResultSet is empty but database contains data for the given sql statement

public ArrayList<AddBean> listPendingTasks() {
java.util.Date date = new java.util.Date();
String modifiedDate= new SimpleDateFormat("yyyy-MM-dd").format(date);
Connection con = JDBCHelper.getConnection();
PreparedStatement ps_sel = null;
ArrayList<AddBean> c = new ArrayList<AddBean>();
ResultSet rs = null;
try {
    ps_sel = con.prepareStatement("select * from tasks where enddate > ? order by enddate");
    ps_sel.setString(2, modifiedDate);
    ps_sel.execute();
    rs = ps_sel.getResultSet();
    while(rs.next())
    {
        AddBean ab = new AddBean();
        ab.setTname(rs.getString(2));
        ab.setCategory(rs.getString(3));
        ab.setStartdate(rs.getString(4));
        ab.setEnddate(rs.getString(5));
        ab.setDescription(rs.getString(6));
        ab.setPriority(rs.getString(7));
        c.add(ab);
    }

    return c;
} catch (SQLException e) {
    e.printStackTrace();
    return null;
}

Yash · Accepted Answer · 2018-11-27T07:25:28.867

try this

If your table has a column of type DATE:

Query : SELECT * FROM [values] v WHERE date >= '2013-06-03';

Then use this method java.sql.Date.valueOf(java.lang.String)

ps.setDate(2, java.sql.Date.valueOf("2015-09-13"));

Updated with Date and Time 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'. MySQL retrieves and displays TIME values in 'HH:MM:SS' format. TIME values may range from '-838:59:59' to '838:59:59'.

String QUERY = "UPDATE TABLE_NAME SET dateValue=?, timeValue=?, timeStampText=? WHERE rowIdentify=?";
PreparedStatement pstmt = con.prepareStatement( QUERY );
    pstmt.setDate(1, java.sql.Date.valueOf( localDate )); // DATE Type
    pstmt.setTime(2, java.sql.Time.valueOf( localTime )); // TIME Type
    pstmt.setString(3, localDateTime ); // TEXT Type
    pstmt.setString(4, conditionValue );

Sample code to get the above Date fields value. ^{getDateFromEpochTime()}

long startTime=System.currentTimeMillis();
String str = getDateFromEpochTime(startTime);
CharSequence charSequence = str.subSequence(0, str.length());
String pattern = "yyyy-MM-dd HH:mm:ss";
DateTimeFormatter dateTimeFormatter = java.time.format.DateTimeFormatter.ofPattern( pattern );
LocalDate localDate = java.time.LocalDate.parse( charSequence, dateTimeFormatter );
LocalTime localTime = java.time.LocalTime.parse( charSequence, dateTimeFormatter );
LocalDateTime localDateTime = LocalDateTime.parse( charSequence, dateTimeFormatter);

Java SQL driver doesnot support java.sql.Timestamp for DATETIME or TIMESTAMP type of Table column. Try to use PreparedStatement when query contains Date and Time Types types.

score -1 · Answer 2 · answered Oct 08 '15 at 11:10

learn the date format of the db. hsql db supports 'yyyy-mm-dd'. If you are using prepared statements, then you can input date as a String or a date. that is not going to make a difference. I recommend using string. Using date is a little complicated for beginners because its not util date. Its sql date. Date formats can sometime be troublesome.

input date using jdbc to hsqldb

2 Answers2