mysql date syntax issue

Question

I'm new to MySql database and currently facing an issue with date format. I have tried few solutions from the internet but nothing worked. Hence I'm posting the question here.

I have a GUI where I'm providing the date as input, which is in "mm/dd/yyyy" format.

This is my code which contains the query:

  public boolean fetchAPReportDomino(Date edDate, APReport apReport ){
    Statement stmt=null;
    ResultSet rs=null;
    SimpleDateFormat sd= new SimpleDateFormat("MM/dd/yyyy");
    SimpleDateFormat sd1=new SimpleDateFormat("yyyy-MM-dd");
    String tempDate=sd.format(edDate);
    if(getConnection()!=null){

            try {
                stmt= getConnection().createStatement();

               String query="SELECT * FROM AP_Report where Edition Date = " + tempDate;
               rs = stmt.executeQuery(query);
               while (rs.next()) {
                   apReport.setRoomHumidity(rs.getInt("Room Humidity"));
                   apReport.setRoomTemperature(rs.getInt("Room Temperature"));
               }
               rs.close();
               stmt.close();

            } catch (SQLException ex) {
                Logger.getLogger(DataService.class.getName()).log(Level.SEVERE, null, ex);
                return false;
            }
    }
    return true;
}

from the MySql workbench, I have tried changing the date field to "DATE", "TIMESTAMP", "DATETIME" datatypes. But nothing has helped.

Below is the error which Im seeing when I execute the code:

SEVERE: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Date = 02/14/2015' at line 1

Posting screenshot of table structure below:

http://prntscr.com/6qz5my

Learn about prepared statements, and pass the Date as a parameter of type java.sql.Date. Not by concatenating a String: docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html — JB Nizet
Also, your date field on database can't be Edition Date (with space) unless it was created with backticks so you have to use it! — Jorge Campos

Jorge Campos Jorge Campos · Accepted Answer · 2015-04-08T03:35:21

First do not ever create database fields like it was simple names with spaces (e.g. Edition Date) in between the words this is a very bad practice and it makes the use of it even worse while programming. This is the image you added in the comments: enter image description here

I strongly recommend you to change that to something like edition_date. the database fields do not need to be pretty just understandable.

Second since you created the fields like that you have to use backtics ` in all queries you run on the database with that fields otherwise the sql parser does not know how to recognize a field that has a name like Edition Date

The error you are seeing:

...server version for the right syntax to use near 'Date = 02/14/2015' at line 1

States exactly what I said about the fields names it show you 'Date = 02/14/2015' because the sql parser think that the Edition word is a command or an operator so the rest it can't recognize.

The right way to use it on the query you create is like this:

String query="SELECT * FROM AP_Report where `Edition Date` = " + tempDate;

See the backticks?

But this way is still wrong because your query is a string without a Prepared Statement which means that you have to wrap your value tempDate with single quote so the right way is:

String query="SELECT * FROM AP_Report where `Edition Date` = '" + tempDate + "'";

But it still have a problem, the default mysql date format for compare as string is yyyy-MM-dd see it here: Date Example (the second query gets nothing because of the format)

On your code you are formating the date that is to be used on the query with this code:

SimpleDateFormat sd= new SimpleDateFormat("MM/dd/yyyy");
SimpleDateFormat sd1=new SimpleDateFormat("yyyy-MM-dd");
String tempDate=sd.format(edDate);

You are using sd on your query when you should use sd1 so

String tempDate=sd1.format(edDate);

After that you can use the query as:

String query="SELECT * FROM AP_Report where `Edition Date` = '" + tempDate + "'";

Note: I don't really remember if you have to use the backticks when retrieving the value here apReport.setRoomHumidity(rs.getInt("Room Humidity")); so you should test as apReport.setRoomHumidity(rs.getInt("`Room Humidity`")); if the first way does not work.

And for the sake of knowledge you should consider in use prepared statement which in your code would be something like:

//...

String query="SELECT * FROM AP_Report where `Edition Date` = ?"
PreparedStatement pstmt = getConnection().prepareStatement(query);

//note that depending on your date type you will need just one 
//of the lines below

//if your edDate is a java.util.Date use as:
pstmt.setDate(new java.sql.Date(edDate.getTime()));
//if it is already a java.sql.Date
pstmt.setDate(edDate);

rs = pstmt.executeQuery(query);

//...

Hope it clears things out to you.

mysql date syntax issue

4 Answers