Why querying a date BC is changed to AD in Java?

Question

My program in Java connects to a Database (Oracle XE 11g) which contains many dates (date format of OracleXE is set to syyyy/mm/dd).

Doing a query in the database with negative dates (before Christ) works fine. When I do it in Java, they are all changed to AD (Anno Domini). How can I retrieve dates in Java respecting AD/BC?

My Java code here does the query to the DB and puts the result in a table.

try {
    Object item=cbPD.getSelectedItem();                                 
    String dacercare=item.toString();
    query = "SELECT DISTINCT PD.Titolo,PD.Inizio,(Select E.nome From Evento E WHERE PD.Inizio_Evento=E.CODE),
            PD.Fine, (Select E.nome From Evento E  WHERE PD.Fine_Evento=E.CODE ) FROM Periododelimitato PD WHERE PD.Titolo=?";
    PreparedStatement stAccess = Login.connessione.prepareStatement(query, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
    stAccess.setString(1,dacercare);
    rset = stAccess.executeQuery(); 
    j = modelPD.getRowCount();
    for (i=0; i<j; i++) modelPD.removeRow(0);
    Date data;
    while (rset.next()) {
        data = rset.getDate(2);
        modelPD.addRow(new Object[]{rset.getString(1),data, rset.getString(3), rset.getString(4), rset.getString(5)});
    }       
}

Here an Example using a specific Query:

try {
    query = "SELECT PD.Inizio FROM PeriodoDelimitato PD WHERE PD.CodP=?";
    String dacercare="8"; //look for record with this specific Primary key
    PreparedStatement stAccess = Login.connessione.prepareStatement(query,
            ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
    stAccess.setString(1, dacercare);
    rset = stAccess.executeQuery(); 
    while(rset.next()) {
        Date dateBC = rset.getDate(1);
        modelPD.addRow(new Object[]{null, dateBC, null, null, null});
    }

Output in Java is:

0509-01-01

Output using the same query (substituing ? with the primary key specified) in Sql developer:

-0509/01/01

Note on the query: the column selected in this example is in Oracle a DATE type.

Adding information: DBMS is Oracle (XE 11g), DB has been built on IDE (SQL developer). The program is written in Java through Netbeans 8.2. I connect to the database in Netbeans adding the Library "ojdbc6.jar".

Check if your driver is JDBC 4.2 (or higher) and try to use `getObject(2, LocalDateTime.class)`. `java.sql.Date` is based around milliseconds since 1-1-1970. — Mark Rotteveel, Oct 19 '17 at 16:55
Why are you using `ojdbc6.jar`? Are you really running on Java 6? — Andreas, Oct 19 '17 at 16:56
@MarkRotteveel for JDBC version "Driver Version: 11.2.0.4.0", i didn't understand how to use getObject in my case, can you exlain it? — StefanoF, Oct 19 '17 at 17:32
@Andreas teacher said to use that one, may it cause the problem? — StefanoF, Oct 19 '17 at 17:33
Could you please indent your code properly and break the lines so not a lot of code disappears offscreen to the right? Thanks in advance. — Ole V.V., Oct 19 '17 at 18:05
Also, maybe you can reproduce your problem using a simpler query? See [How to create a Minimal, Complete, and Verifiable example](https://stackoverflow.com/help/mcve). — Ole V.V., Oct 19 '17 at 18:08
@OleV.V. I added a simpler example with a simple query for PK specified. I tried to break lines too, hope it is better now. Thank you for patience — StefanoF, Oct 19 '17 at 19:04
An MCVE would be all the SQL needed to create a little table, with fewest fields, add a few records, and perform the query, while ignoring all you other business-problem details. — Basil Bourque, Oct 19 '17 at 19:18
@BasilBourque the type of the column selected by the example query is DATE in Oracle — StefanoF, Oct 19 '17 at 19:27
@StefanoF Post such further details as edits to the Question rather than as comments. — Basil Bourque, Oct 19 '17 at 19:31
@BasilBourque Edited, thank you, hope it is better explained now — StefanoF, Oct 19 '17 at 19:46
Can you extract the year number from your resulting date object, to determine if this is a *formatting* problem versus an *erroneous data* problem? — Basil Bourque, Oct 19 '17 at 22:16
By the way, be aware that your use of either `java.sql.Date` or `java.util.Date` is not recommended. Those troublesome old classes are now supplanted by the java.time classes. — Basil Bourque, Oct 19 '17 at 22:17
Thanks for following our suggestions, @StefanoF. It greatly improved the question. I believe I further improved your code indentation. — Ole V.V., Oct 20 '17 at 07:26

score 1 · Answer 1 · answered Oct 20 '17 at 07:11

First, it’s not immediately clear how you should handle historic and not least prehistoric dates and how you should expect them to behave. It’s not something I know, but I doubt that any calendar in common use today was used in the 6th century BCE (before the common era, “BC”). Maybe you were already aware, I just wanted to mention it for anyone else reading this answer.

With thanks to Basil Bourque’s (now deleted) answer, what you have observed seems to be the intended behaviour with java.sql.Date. I tried printing dates from year 2 CE (common era, “AD”) and then year 2 BCE and compared. First 2 CE:

    LocalDate ld = LocalDate.of(2, 1, 1);
    java.sql.Date sqlDate = java.sql.Date.valueOf(ld);
    System.out.println("java.sql.Date " + sqlDate + " millis " + sqlDate.getTime());

java.sql.Date 0002-01-01 millis -62104237200000

This is as expected. For 2 BCE we need to supply -1 to LocalDate since 0 means 1 BCE, and -1 means 2 BCE. Insert LocalDate.of(-1, 1, 1) in the above code, and the output is

java.sql.Date 0002-01-01 millis -62198931600000

We note that the date is printed the same. 0002 is hardly downright incorrect, but it doesn’t tell us whether it’s year 2 CE or BCE. I believe that this explains the behaviour you observed. Next we note that the millisecond values are different, so the dates are different as they should be. The diffirence is 94694400000 milliseconds, which equals 1096 days or 3 years if one of them is a leap year. The leap year may surprise, but otherwise I think it’s correct.

There is something fishy, though. When I converted the sql date back into a LocalDate, the era was lost, I always got a date in the common era. Since you don’t need this conversion, you probably don’t need to care.

I believe the good solution will be to drop the outdated Date class completely and use the modern LocalDate throughout. You should be aware that this follows the so-called proleptic Gregorian calendar, which may not always give the exact same dates as Date. Also this requires JDBC 4.2 compliant driver, so your ojdbc6.jar won’t do. Even though this may mean you’re prevented, I am letting the suggestion stand for anyone else reading along. I have not tested, but I think the following should work:

LocalDate dateBC = rset.getObject(1, LocalDate.class);

Thank you for your time, i found a solution that is working with ojdbc6.jar, if it had not worked i think i would have followed your suggestion to drop the old library talking to the teacher. — StefanoF, Oct 20 '17 at 09:26

score 0 · Answer 2 · answered Oct 20 '17 at 09:33

0

A solution using the old Date type to query SQL dates BC and AC that is working is to declare into my class a SimpleDataFormat with the format specified below

public SimpleDateFormat sdf= new SimpleDateFormat("yyyy-MM-dd G");

Then I declared a Date dataOUT invoking the format method of SimpleDataFormat giving as input the Date BC queried from the Database

dataOUT=sdf.format(rset.getDate(2));

Thank you all for the time dedicated to my question!

answered Oct 20 '17 at 09:33

StefanoF

21
5

Thank you for sharing your solution. Insisting on the old classes this solution is the correct one and will give an output like `0509-01-01 BC`. I still recommend the modern API. :-) – Ole V.V. Oct 20 '17 at 09:59

Why querying a date BC is changed to AD in Java?

2 Answers2

Linked