Converting input date string to SQL date type

Question

I have looked all over for this answer for a whole day but I'm not getting anywhere.

I have a string that is formatted like this:

dd/mm/yyyy

I want to enter this in to a SQL database

The database field (of type Date) shows the format as yyyy-mm-dd.

At the moment I have the following code:

    String inputDate = dateBookingEntry.getText();
    LocalDate interDate = LocalDate.now();
    interDate.parse(inputDate,DateTimeFormatter.ofPattern("dd/MM/yyyy"));
    java.sql.Date finalDate = java.sql.Date.valueOf(interDate);

When I enter the date 12/12/2020 as the string, I get the error message:

java.sql.SQLSyntaxErrorException: 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 ''2020-01-29'

I don't get what I'm doing wrong, or how to fix it. I get that it's showing the current date and not the entered date, but even if that was the problem, why would it show an SQL Syntax error?

What should I change here for it to work?

EDIT:

The SQL insertion code to this table looks like this:

st.executeUpdate("INSERT INTO `fitnessClasses`(`session`, `date`, `price`, `trainer`, `level`, `type`) VALUES ('"+session+"', '"+sqlDate+"', '"+price+"', '"+trainer+"', '"+level+"', '"+classType+"')");

The full SQL code mentioned above with the error looks like this:

ResultSet result = st.executeQuery("SELECT * FROM `fitnessClasses` WHERE `session` = '"+session+"' AND `date` '"+finalDate+"' AND `type` = '"+classType+"' AND `level` = '"+level+"'");

EDIT 2:

OK, I now realise I was missing an =.

Now the SQL SELECT works but it searches for today's date and not the entered date

Looks like your query (String?) is malformed... Can you show (how you build) it? The error message shows a correctly formatted date with leading double quotes and a trailing single quote. I think that won't work in any SQL dialect... Might be two single quotes trailing... However, SQL doesn't like that. — deHaar
– deHaar, Commented Jan 29, 2020 at 15:44
Please show us the INSERT statement and the way you execute it — user330315
– user330315, Commented Jan 29, 2020 at 15:45
you might change (this date column ) it into string instead of time in your db then you could insert it easily — user10224616
– user10224616, Commented Jan 29, 2020 at 15:49
I was missing an =. Now my only issue is the fact that it is searching today's date — HarryDylan
– HarryDylan, Commented Jan 29, 2020 at 16:06

AstarAndy · Accepted Answer · 2020-01-29 17:02:15Z

1

I think you want the class-level of parse such as

LocalDate interDate = LocalDate.parse(inputDate blah blah)

Currently you are setting interDate to now() which is the current date and that is why you end-up with today's date and not the date entered.

answered Jan 29, 2020 at 17:02

AstarAndy

1211 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Converting input date string to SQL date type

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