0

So I'm trying to get a basic sql string to work where it will grab the records in the sqlite database based on between dates. However, for some reason, it doesn't work. I just don't understand why.

private void viewTransactionsBetweenDatesTable(){
    //Sets the table to view transactions between certain dates

    try{
        //Get's the dates from startDateChooserTransactions1 and endDateChooserTransactions1
        DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
        DateFormat df2 = new SimpleDateFormat("MMM dd, yyyy");
        Date sdct = startDateChooserTransactions1.getDate();
        Date edct = endDateChooserTransactions1.getDate();
        String sdcts = df.format(sdct);
        String edcts = df.format(edct);
        String sdctlabel = df2.format(sdct);
        String edctlabel = df2.format(edct);

        //Child's ID
        String cid = childIDCheck1.getText();

        //Grab's the specified data and places that as the table
        String sql = "SELECT * FROM ChildrenPayment WHERE ChildID='"+cid+"' AND strftime('%Y-%m-%d', 'Report Transaction Date') BETWEEN '"+sdcts+"' AND '"+edcts+"' ";
        pst = conn.prepareStatement(sql);
        rs = pst.executeQuery();

        //Sets up the table
        Info1.setModel(DbUtils.resultSetToTableModel(rs));    
        TableColumnModel tcm = Info1.getColumnModel();
        //tcm.removeColumn(tcm.getColumn(3));
       // tcm.removeColumn(tcm.getColumn(3));
       // tcm.removeColumn(tcm.getColumn(10));
       // tcm.moveColumn(11, 10);
       // tcm.removeColum(tcm.getColumn(13));

        //Changes modLabel1
        modLabel1.setText(firstNameEditClass1.getText() + " " + lastNameEditClass1.getText() + " Between " + sdctlabel + " - " + edctlabel);

    }catch(Exception e){
        JOptionPane.showMessageDialog(null, e);
    }finally{
        try{
            pst.close();
            rs.close();
        }catch(Exception e){
            JOptionPane.showMessageDialog(null, e);
        }
    }
}

I am using a jdatechooser so I am sort of forced to use SimpleDateFormat compared to the better DateTimeFormatter. Anyway, I'm formatting it according to YYYY-MM-DD like sqlite likes, but when I run the function, the table does not display anything. I set the days pretty wide (Feb 01, 2018 to Feb 14, 2018) and the date in my database is Feb 07, 2018. I have a few records in the database for it to pull. However, it just doesn't do it. And no error is popping up, so I do not know why it is not working.

Image of the records that I'm trying to place into my jtable

Edit1: Before I forget, I also tried the following SQL string

String sql = "SELECT * FROM ChildrenPayment WHERE ChildID='"+cid+"' AND 'Report Transaction Date' BETWEEN '"+sdcts+"' AND '"+edcts+"' ";
Xevon
  • 15
  • 1
  • 6

1 Answers1

0

This will not work:

strftime('%Y-%m-%d', 'Report Transaction Date')

because the format specifiers you have provided require that you supply three values, one each for year, month, and day.

If the dates in the database are stored as complete SQLite datetime strings, you will have to use

"... date([Report Transaction Date]) BETWEEN '"+sdcts+"' AND '"+edcts+"' ";

Note square brackets (not single quotes) around column name. This has nothing to do with needing a date/time value, it's because the column name has spaces in it. Any column name with spaces has to be enclosed in double quotes or square brackets. That's why it's a good idea to never use spaces in column names.

If they are, in fact, stored as 'YYYY-MM-DD' strings, then the reason your alternative didn't work is because you single-quoted the column name 'Report Transaction Date', which results in comparing that literal string to the date values.

Mark Benningfield
  • 2,800
  • 9
  • 31
  • 31
  • So I've tested it in the manner that you said with the second code block, but I got the following error: `java.sql.SQL.Exception: near "Transaction": syntax error` Also, the dates are stored as 'YYYY-MM-DD', but the type is DATETIME, do I need to transition it over to TEXT? – Xevon Feb 08 '18 at 01:41
  • Right, that's because you have spaces in your column name. Use either double quotes, or square brackets. `"Report Transaction Date"` or `[Report Transaction Date]` – Mark Benningfield Feb 08 '18 at 01:45
  • By default, SQLite treats the DATETIME type alias (SQLite doesn't have a native DATETIME type) as NUMERIC column affinity. However, the specific Java wrapper library that you're using may handle the conversion to TEXT instead of NUMERIC. If the data is in text, then I wouldn't worry about it. – Mark Benningfield Feb 08 '18 at 01:58
  • Once I added in the brackets, it worked! Thank you very much for helping me with my solution. Just so that I understand the basic principal, if you are trying to get a date from DATETIME and a column has a name, you need to use brackets instead of single quotes for names with spaces.Would that still apply if the column was a TEXT instead of a DATETIME? Also, thank you so much for the help. I upvoted, but they don't recognize it since I'm such low rank. – Xevon Feb 08 '18 at 02:36
  • Sorry, my comment came late and the page doesn't auto refresh. – Xevon Feb 08 '18 at 02:37
  • I'll edit the answer to try and make this clearer. Also, you'll have to wait a few minutes to upvote or accept (only if you want to). – Mark Benningfield Feb 08 '18 at 02:49
  • I've accepted the answer. I understand that design wise, it is terrible to have column names with spaces, instead it would be better to make the column name reportTransactionDate or report_Transaction_Date, etc. However, when I started this program, I was (and still am new to learning Java and SQL) so I've bad syntax. I'm attempting to change it. Another quick question: Design wise, it is better to have column names without spaces and then after setting the jtable's model to the same as the database table, manually change each column name to what you desire? – Xevon Feb 08 '18 at 02:58