Converting java.util.Date to java.sql.Date resulting in date changes [duplicate]

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
– Dawood ibn Kareem, Commented Nov 7, 2019 at 2: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
– QWERTY, Commented Nov 7, 2019 at 2:26
I also recommend using PreparedStatement instead of building your SQL via string concatenation. For all sorts of reasons. — Dawood ibn Kareem
– Dawood ibn Kareem, Commented Nov 7, 2019 at 2:36
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. Specifically select * from table1 where last_login_dt < ?; and yourPreparedStatement.setObject(LocalDate.parse("2019-01-31"));. — Anonymous
– Anonymous, Commented Nov 7, 2019 at 3:37

LHCHIN · Accepted Answer · 2019-11-08 14:12:52Z

1

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.

edited Nov 8, 2019 at 14:12

answered Nov 7, 2019 at 3:27

LHCHIN

4,0782 gold badges19 silver badges37 bronze badges

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

2 Comments

QWERTY Over a year ago

Thanks so much for the clear explanation! Let me test it and get back to you :)

LHCHIN Over a year ago

@hyperfkcb You're welcome! But why you unaccepted and unupvoted for my answer? Does not it work for you?

Collectives™ on Stack Overflow

Converting java.util.Date to java.sql.Date resulting in date changes [duplicate]

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related