java-How should one pass a date value to MySQL?

Question

I have a column field called StartDate in MySQL of Date datatype. In my application, I defined a way to show the current date onn my page like this.

String today = CoreUtil.parseDate(new Date());

This basically returns the date in YYYY-MM-DD format and stored it in a string which is fine.

Now, I would like to pass this value into a function that inserts the value to the column StartDate.

The function I declared is as follow:

public void insert_update(String nodeid,String ts,Date startdt,Date enddt,int enable)

I am calling this function and passing the value today to it like below:

 fileFacade.insert_update(...,....,today,....,...);

Now I am not an expert in this date thingy and seeing it wouldn't allow me to pass the value as I defined it as Date parameter, how should I handle this issue?

Should I convert it back to Date format and pass the value or does MySQL allow insertion of string value into a Date datatype column?

I just want to store the value in my table in YYYY-MM-DD format.

edit:

My function:

public void insert_update(String nodeid,String ts,Date startdt,Date enddt,int enable){
     try {

         //   UrlLink attr = em.find(UrlLink.class,n);


         String sql="UPDATE urllink SET STARTDT="+startdt+",ENDDT="+enddt+",ENABLE="+enable+"WHERE URL='f0="+nodeid+"&ts="+ts + "'";

         em.createNativeQuery(sql).executeUpdate();




     }catch (Exception e) {
         e.printStackTrace();
     }

 }

I don't need the format to have time in my table, just in the format of `YYYY-MM-DD` — Daredevil, Mar 25 '19 at 09:17
1. Respect the Java naming conventions 2. Stop using Date. It's clumsy and obsolete. Use a java.time.LocalDate. 3. If a method expects a [Local]Date, you can't pass it a String. You must pass it a [Local]Date. Just don't transform the [Local]Date to a String in the first place. pass it directly. And use a prepared statement to pass the LocalDate as a parameter. You shouldn't transform the date to a string to insert it in the database. — JB Nizet, Mar 25 '19 at 09:20
None of the above. Don't. Don't create the date yourself. Let the database do it, with CURRENT_DATE in the SQL. And store it as a `DATE` type, not as ASCII. Simplify your life. — user207421, Mar 25 '19 at 09:33
Possible duplicate of [Insert & fetch java.time.LocalDate objects to/from an SQL database such as H2](https://stackoverflow.com/questions/43039614/insert-fetch-java-time-localdate-objects-to-from-an-sql-database-such-as-h2). You may also find [may answer to a different question here](https://stackoverflow.com/a/55312215/5772882) useful. — Ole V.V., Mar 25 '19 at 14:38
I recommend you don’t use and `Date`, no matter if you mean `java.util.Date` or `java.sql.Date`. Those classes are poorly designed and long outdated. Instead use `LocalDate` from [java.time, the modern Java date and time API](https://docs.oracle.com/javase/tutorial/datetime/). — Ole V.V., Mar 25 '19 at 14:47
As an aside I don’t understand how your `insert_update` works. I cannot see valid SQL syntax coming out of the string concatenation involving `Date` objects in the method. Maybe it’s just me. — Ole V.V., Mar 25 '19 at 14:58
@OleV.V. It's just a function that I call and pass in the values that I want and then it update the table based on the parameters I passed. Hope this is clear. — Daredevil, Mar 26 '19 at 01:49

Darshan Mehta · Answer 1 · 2019-03-25T09:30:09.680

0

You should not try to convert String into a Date in order to insert it into a table. You can use date type as is using preparedStatement, e.g.:

Date date = new Date();
PreparedStatement pStmt = connection.prepareStatement("<query>");
pStmt.setDate(1, date);

Here's the javadoc.

Update

If the purpose here is to display the date in specified format then I would strongly recommend storing the date as DATE in MySQL in format it while querying. The decision of how we store the date should not be driven by how it needs to be displayed on Front End as we might need different date formats to be displayed across multiple User Interfaces.

edited Mar 25 '19 at 09:30

answered Mar 25 '19 at 09:18

Darshan Mehta

30,102
11
68
102

But this includes hours,mins and sec too. I don't want that – Daredevil Mar 25 '19 at 09:19
It will be taken care by MySQL's date type. If you don't want hours, minutes etc in query, you can just select the date part. – Darshan Mehta Mar 25 '19 at 09:20
Oh I'm not using preparedStatement, just want to know the correct way to pass my argument to the function – Daredevil Mar 25 '19 at 09:22
the function accepts `Date` and hence, the correct way to pass an argument to it is to pass a `Date` object. If the function accepts a date, it's upto that function to store it into MySQL. – Darshan Mehta Mar 25 '19 at 09:23
You mean I should pass `date` directly to the function parameter? – Daredevil Mar 25 '19 at 09:24
Yes, exactly. You should not bother about converting it into String. – Darshan Mehta Mar 25 '19 at 09:27
I debug `Date date=new Date()` and it shows Mon Mar 25 though. – Daredevil Mar 25 '19 at 09:27
Is there a way I can display it as `YYYY-MM-DD` on my front end? – Daredevil Mar 25 '19 at 09:27
Yes, you can format it while fetching it from database. I would recommend not to store the date in predefined format just for display purpose. – Darshan Mehta Mar 25 '19 at 09:28
Yea it's showing `Mon Mar 25 17:26:46 SGT 2019` on my display. Is there a way to make it show in the format i want ? – Daredevil Mar 25 '19 at 09:30
Yes, how are you fetching the date from the database? – Darshan Mehta Mar 25 '19 at 09:30
So the problem is I want to show the date format in `YYYY-MM-DD` and also insert into database in the exact same format. – Daredevil Mar 25 '19 at 09:32

score -1 · Answer 2 · edited Mar 25 '19 at 09:34

-1

Pass String date in method parameter instead of Date type and then format that date type like this:

SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd",Locale.ENGLISH);

formatter.parse(date)

edited Mar 25 '19 at 09:34

Mebin Joe

2,172
4
16
22

answered Mar 25 '19 at 09:21

Muhammad bux

36
1
6

Thanks for wanting to contribute. Please don’t teach the young ones to use the long outdated and notoriously troublesome `SimpleDateFormat` class. At least not as the first option. And not without any reservation. Today we have so much better in [`java.time`, the modern Java date and time API](https://docs.oracle.com/javase/tutorial/datetime/) and its `DateTimeFormatter`. And also do keep and pass your date in a `LocalDate`, not in a `String`. – Ole V.V. Mar 25 '19 at 14:44

java-How should one pass a date value to MySQL?

2 Answers2