CODEWITHSUNDEEP
javamysqlsqldateresultset
Martina
Martina

Reputation: 1918

DATE FORMAT SQL

I have a table called Abbonamento that has the following attributes:

Abbonamento(idAbbonamento, tipo, DataInizio, DataScadenza, ....)

DataInizio and DataScadenza are of type DATE. The problem borns when I do a select on this table:

        String queryAbb = "select idabbonamento, tipo, DATE_FORMAT(datainizio,'%d-%m-%Y'), DATE_FORMAT(datascadenza,'%d-%m-%Y'), ...;
                       prest = con.prepareStatement(queryAbb);
        rs = prest.executeQuery();
        while (rs.next()) {
            a=new Abbonamento();
            a.setIdAbbonamento(rs.getInt(1));
            a.setTipo(rs.getString(2));
            a.setDataInizio(rs.getDate(3));
            System.out.println(rs.getDate(3)); 
            a.setDataScadenza(rs.getDate(4));
            ...
        }

Now, if the date DataInizio in the db is for example 2013-11-05 00:00:00 I would like to have 05-11-2013 but the println prints 0004-10-13.

What's wrong with the code above?

Upvotes: 2

Views: 1039

Answers (3)

gaborsch
gaborsch

Reputation: 15758

Instead of 

rs.getDate(3)

you should use 

rs.getString(3)

because the data is already formatted as String. If you want to get as Date, first, a Date object is created from your 05-11-2013 string, then you receive that.

If you have Date objects in your objects, you should either parse() the returnes string with the same format you returned from the DB, or let the JDBC do the conversion for you (in this case, simply select idabbonamento, tipo, datainizio, ...) without formatting.

I recommend that let JDBC do it. Less user code, less trouble :)

String queryAbb = "select idabbonamento, tipo, datainizio, datascadenza, ...";
...
a.setDataInizio(rs.getDate(3));
// reading the formatted data:
System.out.println(new SimpleDateFormat().format(a.getDataInizio());

Actually, there is one more trick, but you don't have to care about: rs.getDate() returns java.sql.Date, but you probably use java.util.Date. That's not a problem, because java.sql.Date is a subclass of java.util.Date, so this assignment is totally valid.

Upvotes: 1

Oscar Pérez
Oscar Pérez

Reputation: 4397

In a database-independent way, you could use java.text.SimpleDateFormat. For example:

java.util.Date date = rs.getDate(3);
String dateFormatted = (new java.text.SimpleDateFormat()).format(date);
a.setDataInizio(dateFormatted);

Upvotes: 0

M.Kreusburg
M.Kreusburg

Reputation: 19

Try this:

select idabbonamento, tipo, convert(char(10), datainizio, 105)....

Upvotes: 0

Related Questions