Converting java.util.Date to java.sql.Date resulting in date changes

Question

I was having some problem when trying to convert java.util.Date to java.sql.Date. My date parameter passed in was originally a string in this format: 2019-01-31. Then I am trying to convert it to java.util.date using this code:

    SimpleDateFormat formatter = new SimpleDateFormat("YYYY-MM-DD");
    Date date = null;
    try {
        date =  formatter.parse(this._dDate);
    } catch (ParseException e) {
        e.printStackTrace();
    }

In my SQL statement, I am trying to convert it to java.sql.Date:

buffer.append("SELECT * FROM TABLE1 WHERE LAST_LOGIN_DT < ");
buffer.append("TO_DATE('" + new java.sql.Date(date.getTime()) + "','YYYY-MM-DD') ");

However, when I printed out the buffer in string, I realized that the date is changed. For instance, the data string I passed in is 2018-01-01, but in the SQL statement it became 2017-12-31. Any ideas?

Thanks!

You wanted `"yyyy-MM-dd"`. But don't do this. Go straight from your `String` to your `java.sql.Date`. It will save you a great deal of stress. — Dawood ibn Kareem, Nov 07 '19 at 02:24
@DawoodsaysreinstateMonica Sorry but you mean just change the SQL to "TO_DATE('" + new java.sql.Date(date) + "','yyyy-MM-dd') " and remove the date conversion code instead? — QWERTY, Nov 07 '19 at 02:26
No, that's not what I meant. Since `_dDate` contains a `String` in the right format, you could just write `buffer.append("TO_DATE('" + new java.sql.Date(_dDate)) + "','YYYY-MM-DD') ");`. Your alternative is just to change `YYYY-MM-DD` to `yyyy-MM-dd` in the `SimpleDateFormat` (not in the SQL). — Dawood ibn Kareem, Nov 07 '19 at 02:34
I also recommend using `PreparedStatement` instead of building your SQL via string concatenation. For all sorts of reasons. — Dawood ibn Kareem, Nov 07 '19 at 02:36
Just use `java.sql.Date.valueOf(this._dDate)` with `yyyy-MM-dd` format for conversion directly. — LHCHIN, Nov 07 '19 at 02:37
But the _dDate is in String format, if I changed to new java.sql.Date(_dDate), it is highlighted as syntax error — QWERTY, Nov 07 '19 at 02:38
Another option is [JDBC escape syntax](https://www.tutorialspoint.com/what-is-jdbc-sql-escape-syntax-explain) `SELECT * FROM TABLE1 WHERE LAST_LOGIN_DT < {d '2018-01-01'}` — Abra, Nov 07 '19 at 02:41
@LHCHIN So you mean like this: "TO_DATE('" + java.sql.Date.valueOf(this._dDate)) + "','yyyy-MM-dd') " ? — QWERTY, Nov 07 '19 at 02:58
@hyperfkcb I think both `TO_DATE('" + java.sql.Date.valueOf(this._dDate)) + "','yyyy-MM-dd')` and `TO_DATE('" + java.sql.Date.valueOf(this._dDate)) + "','YYYY-MM-DD')` should work. The key is the format of `this._dDate` must be `yyyy-MM-dd`. — LHCHIN, Nov 07 '19 at 03:05
@LHCHIN I see I see so that means I need to change the date format in date conversion part to yyyy-MM-dd? — QWERTY, Nov 07 '19 at 03:07
@hyperfkcb YES, you got it! But the better way is directly using `java.sql.Date.valueOf(this._dDate)` without converting `util.Date` to `sql.Date`. — LHCHIN, Nov 07 '19 at 03:11
did you try doing a System.out on the converted java.util.Date and saw the result. It will be something like Sun Dec 31 00:00:00 UTC 2017 . That is what goes to sql.Date as you "converted the date without any timezone info, it assumed the default time zome) — Optional, Nov 07 '19 at 03:15
I recommend you don’t use any of the two `Date` classes. They are poorly designed and long outdated. Instead just use `LocalDate` from [java.time, the modern Java date and time API](https://docs.oracle.com/javase/tutorial/datetime/). Specifically `select * from table1 where last_login_dt < ?;` and `yourPreparedStatement.setObject(LocalDate.parse("2019-01-31"));`. — Ole V.V., Nov 07 '19 at 03:37
* That should have been `yourPreparedStatement.setObject(1, LocalDate.parse("2019-01-31"));`.(the `setObject` method takes two arguments). — Ole V.V., Nov 07 '19 at 04:49

LHCHIN · Answer 1 · 2019-11-08T14:12:52.670

Let's take a look at following example first:

String date = "2018-01-01";

SimpleDateFormat sdf1 = new SimpleDateFormat("YYYY-MM-DD");
System.out.println(sdf1.parse(date));
System.out.println(sdf1.parse(date).getYear());
System.out.println(sdf1.parse(date).getMonth());
System.out.println(sdf1.parse(date).getDay());

SimpleDateFormat sdf2 = new SimpleDateFormat("yyyy-MM-dd");
System.out.println(sdf2.parse(date));
System.out.println(sdf2.parse(date).getYear());
System.out.println(sdf2.parse(date).getMonth());
System.out.println(sdf2.parse(date).getDay());

Console output:

Sun Dec 31 00:00:00 CST 2017
117
11
0
Mon Jan 01 00:00:00 CST 2018
118
0
1

Now I think you have already noticed the difference!
Quoted from Class SimpleDateFormat:

Y means Week year and D means Day in year.
y means Year and d means Day in month.

yyyy and YYYY both represent a year but yyyy represents the calendar year while YYYY represents the year of the week. And, dd represents the calendar day while DD represents the day of the month. Therefore, there are 2 ways to fix your issue:

Method 1
Change the format for SimpleDateFormat from YYYY-MM-DD to yyyy-MM-dd if you still want to convert this._dDate to java.util.Date then java.sql.Date, but I strongly recommend you don't do this.

Method 2
The better way is directly using java.sql.Date.valueOf(this._dDate) and the format of this._dDate is supposed to be yyyy-MM-dd.

Thanks so much for the clear explanation! Let me test it and get back to you :) — QWERTY, Nov 07 '19 at 04:08
@hyperfkcb You're welcome! But why you unaccepted and unupvoted for my answer? Does not it work for you? — LHCHIN, Nov 11 '19 at 04:54

Converting java.util.Date to java.sql.Date resulting in date changes

1 Answers1

Related