SQL java Select all the records that added in this month

Question

I need to display to the table all the records who has is created_at this month. the format is mmm dd yyyy, Using java and sqlite

I have the column "Start" in my database, if the month of the date is this month, it has to appear in the table.

I Tried this code below,, pls guide me.

i need to select all the records, within the month.

  try {
   SimpleDateFormat dateFormat = new SimpleDateFormat("MM/dd/yyyy");
   Calendar c = Calendar.getInstance();
   c.set(Calendar.DAY_OF_MONTH, 1);
   c = Calendar.getInstance(); // reset
   String today = dateFormat.format(c.getTime());

   int month = c.get(Calendar.MONTH) + 1;

   String sql = "SELECT Firstname FROM Members_Tbl WHERE End = '" + month 
   + "'";
   pst = con.prepareStatement(sql);
   rs = pst.executeQuery();
   monthlyreports.setModel(DbUtils.resultSetToTableModel(rs));

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

Possible duplicate of [how do I get month from date in mysql](https://stackoverflow.com/questions/2039839/how-do-i-get-month-from-date-in-mysql) — Matias Fuentes, Apr 11 '19 at 19:49
Mysql is not the same is sqlite, so \i removed it from your tags. — forpas, Apr 11 '19 at 20:00
In your question you mention the columns `created_at` and `Start` and in your code you have a column `End`. Explain what you need. — forpas, Apr 11 '19 at 20:03
Just need to select a record, where the "start" date is within this month. — doggo, Apr 11 '19 at 20:14
I recommend you don’t use `SimpleDateFormat` and `Calendar`. Those classes are poorly designed and long outdated (the former in particular notoriously troublesome). Instead use `LocalDate` and `DateTimeFormatter`, both from [java.time, the modern Java date and time API](https://docs.oracle.com/javase/tutorial/datetime/). — Ole V.V., Apr 11 '19 at 20:23
Do I remember correctly that SQLite hasn’t got a `date` datatype? So you need a query like `SELECT Firstname FROM Members_Tbl WHERE End like 'April % 2019';`? I’d recommend you store your date strings in ISO 8601 format (`yyyy-mm-dd`). It might also make your query simpler. — Ole V.V., Apr 11 '19 at 20:25

Ole V.V. · Answer 1 · 2019-04-11T21:07:21.253

java.time

I have not tested (haven’t got SQLite installed), but I believe the following should work:

    DateTimeFormatter monthPatternFormatter = DateTimeFormatter.ofPattern("MMM '%' uuuu", Locale.ENGLISH);
    YearMonth currentMonth = YearMonth.now(ZoneId.of("Australia/Melbourne"));
    String sql = "SELECT Firstname FROM Members_Tbl WHERE Start like ?;";
    pst = con.prepareStatement(sql);
    String monthPattern = currentMonth.format(monthPatternFormatter);
    pst.setString(1, monthPattern);
    rs = pst.executeQuery();

I am taking your word for it when you say that the format in your database is mmm dd yyyy, for example Apr 11 2019. I am assuming English month abbreviations.

I would recommend storing ISO 8601 standard date strings in your database, though. It goes like 2019-04-11. Then your format pattern string would be uuuu-MM-'%'. Or you might compare strings using <= and <, which would probably be more efficient.

The date/time classes that you were using, SimpleDateFormat and Calendar, are poorly designed and fortunately long outdated. Instead I am using java.time, the modern Java date and time API. It is so much nicer to work with.

Links

Oracle tutorial: Date Time explaining how to use java.time.
SQLite Like: Querying Data Based On Pattern Matching

score 0 · Answer 2 · answered Apr 11 '19 at 20:23

You could use (see below) the SQLite substr core function :-

String sql = "SELECT Firstname FROM Members_Tbl WHERE substr(Start,1,2) = '" + month + "'";

BUT that would include all years.

So you may want :-

String sql = "SELECT Firstname FROM Members_Tbl WHERE substr(Start,1,2) = '" + month + "' AND substr(Start,7,4) = '" + the_respective_year + "'";

Notes

If MM or DD are not always 2 characters (e.g. 1/9/2019) then the above would not work properly.

The recommended way is to store dates in YYYY-MM-DD format (or any of the time string formats from the link). You can then use the SQLite DateTime functions.

forpas · Answer 3 · 2019-04-12T16:53:44.477

Your SimpleDateFormat object must match the format stored in the table if you want to compare the months.
You must compare also the year not just the month.
It is always safer to use placeholders inside the sql statement instead of concatenating the parameters:

SimpleDateFormat dateFormat = new SimpleDateFormat("MMM dd yyyy");
Calendar c = Calendar.getInstance();
c.set(Calendar.DAY_OF_MONTH, 1);
c = Calendar.getInstance(); // reset
String today = dateFormat.format(c.getTime());
String sql =
        "SELECT Firstname FROM Members_Tbl WHERE " +
        "substr(Start, 1, 3) = ? AND substr(Start, 8, 4) = ?";

pst = con.prepareStatement(sql);
pst.setString(1, today.substring(0, 3));
pst.setString(2, today.substring(7));
rs = pst.executeQuery();
monthlyreports.setModel(DbUtils.resultSetToTableModel(rs));

rs.close();

Edit
Since you changed the format of the dates to YYYY-MM-DD,
you can use this query:

String sql = "select * from Members_Tbl WHERE strftime('%Y-%m', Start) = strftime('%Y-%m', 'now')";

score 0 · Answer 4 · answered Apr 12 '19 at 16:32

0

String sql = "select * from Members_Tbl WHERE strftime('%m', Start) == strftime('%m', 'now')";

This is the query I used, and after several searches the date should be YYYY-MM-DD. thank you everyone for your cooperation.

answered Apr 12 '19 at 16:32

doggo

1
1

This query will give you wrong results: if you execute it say today you will get all the rows for April 2019, April 2018,.. for every April. – forpas Apr 12 '19 at 16:40

SQL java Select all the records that added in this month

4 Answers4

java.time

Notes