topic Re: Wrong date using Java/JDBC (1940-06-14 is missing) in SAS Data Management

Wrong date using Java/JDBC (1940-06-14 is missing)

jespersahner — Sun, 19 Apr 2015 08:54:31 GMT

Hi,

Consider this dataset:

libname ud 'C:\Aktuar';

data ud.datoer;

do datof='1JUN1940'd to '1JUL1940'd;

dato=datof;

output;

end;

format datof yymmdd10.;

run;

The dataset is then read using Java/JDBC:

try {

Class.forName("com.sas.net.sharenet.ShareNetDriver");

Properties props = new Properties();

Connection conn;

Statement stmt;

props.setProperty("librefs", "x '" + "C:/Aktuar" + "'");

conn = DriverManager.getConnection("jdbc:sharenet://w33284:9453/", props);

stmt = conn.createStatement();

ResultSet rs = stmt.executeQuery("select * from " + "x.Datoer");

while (rs.next()) {

Date datof = rs.getDate("datof");

long dato = rs.getLong("dato");

System.out.println(datof+" "+dato);

}

stmt.close();

conn.close();

} catch (ClassNotFoundException | SQLException ex) {

Logger.getLogger(Testing_Datoer.class.getName()).log(Level.SEVERE, null, ex);

}

Output:

1940-06-09 -7144

1940-06-10 -7143

1940-06-11 -7142

1940-06-12 -7141

1940-06-13 -7140

1940-06-15 -7139

1940-06-16 -7138

1940-06-17 -7137

1940-06-18 -7136

1940-06-19 -7135

1940-06-20 -7134

As seen the date 1940-06-14 is missing and the first 5 dates are wrong.

I am running Win 7 + SAS 9.2 + Java 1.8. The problem occurs with JDBC 9.1/9.2/9.3/9.4.

What is going on?

Regards,

Jesper

Re: Wrong date using Java/JDBC (1940-06-14 is missing)

Kurt_Bremser — Mon, 20 Apr 2015 06:23:33 GMT

Since I could verify that the dataset is created correctly both on AIX and Win7, I suggest to open a track with SAS TS. The error could happen in the SAS JDBC driver, after all.

Re: Wrong date using Java/JDBC (1940-06-14 is missing)

PaulHomes — Mon, 20 Apr 2015 08:03:23 GMT

It works for me. I'm using SAS 9.4 M0 with an IOM based JDBC connection rather than SAS/SHARE and I get a continuous sequence of dates including 14Jun1940.

Off the top of my head, I'm wondering if it might be a daylight savings time (DST) interaction where a day increment plus a time change ticks the other side of midnight and loses a date. What time zone are you in? I'm in GMT+10 with no DST. You're setting a day value in the SAS code but the JDBC getDate() method is returning a date & time and so time and DST may be a factor here perhaps? Interestingly, I read that there were significant differences in DST during the 2nd world war - the UK was in British Double Summer Time (BDST) from 1940-1945 for example: British Summer Time - Wikipedia, the free encyclopedia

Re: Wrong date using Java/JDBC (1940-06-14 is missing)

jespersahner — Mon, 20 Apr 2015 09:19:13 GMT

Thanks for you input.

I am in GMT+1 with DST (Copenhagen).

The problem seems to arise beginning with year 1900:

1899-12-27 -21919

1899-12-28 -21918

1899-12-29 -21917

1899-12-30 -21916

1899-12-31 -21915

1900-01-01 -21914

1900-01-01 -21913

1900-01-02 -21912

1900-01-03 -21911

1900-01-04 -21910

1900-01-05 -21909

Notice that 1900-01-01 is repeated. This 1 day lack is then retained until 1940-06-15.

I am able to fix the problem by adding a Calendar:

Date datof = rs.getDate("datof", Calendar.getInstance());

However the behaviour is still very strange and I will open a track with SAS Technical Support.

Regards,

Jesper

Re: Wrong date using Java/JDBC (1940-06-14 is missing)

LinusH — Tue, 21 Apr 2015 08:50:08 GMT

Another way to test this is by using a "standard" application that could read the data directly from the JDBC driver.

Re: Wrong date using Java/JDBC (1940-06-14 is missing)

TomKari — Tue, 21 Apr 2015 13:50:47 GMT

Variables dato and datof are actually the same number in your SAS dataset. I believe the issue is in the "Date datof = rs.getDate("datof");" statement, although I hesitate to call it a bug since adding the calendar fixes it. I don't see any way this is involved with SAS.

I think that if you switch the statements:

Date datof = rs.getDate("dato");

long dato = rs.getLong("datof");

you'll see the same problem in the output datof, even though it's coming from a different variable. Beyond that, my knowledge of Java is too sparse to suggest anything.

Tom

Re: Wrong date using Java/JDBC (1940-06-14 is missing)

jespersahner — Tue, 21 Apr 2015 16:20:54 GMT

Yes, dato and datof are (deliberately) the same number, which shows that the JDBC driver causes the problem.

You can not switch the statements, since "dato" can not be converted to Date and "datof" can not be converted to long, the JDBC driver prevents this as it should.

SAS Technical Support has been able to reproduce the problem so I expect them to fix the JDBC driver.

Re: Wrong date using Java/JDBC (1940-06-14 is missing)

Ksharp — Wed, 22 Apr 2015 12:58:36 GMT

Not sure . You should set you Time Zone to Date Class . I remember I wrote some Java code before to get wrong Date if your Time Zone is not set right .

And Did you check StackOverFlow.com to ask how to get Java Date ? I used to go there to get some Java help.

Xia Keshan

Re: Wrong date using Java/JDBC (1940-06-14 is missing)

jespersahner — Sat, 09 May 2015 04:17:12 GMT

If I change the time zone to UTC-06:00 Central America, no DST observed I get this:

1899-12-29 -21917

1899-12-30 -21916

1899-12-31 -21915

1899-12-31 -21914

1900-01-01 -21913

1900-01-02 -21912

1900-01-03 -21911

1960-04-21 112

1960-04-22 113

1960-04-23 114

1960-04-25 115

1960-04-26 116

1960-04-27 117

Here 1899-12-31 is repeated and now 1960-04-24 is missing, can you reproduce this by change the time zone?

Re: Wrong date using Java/JDBC (1940-06-14 is missing)

jespersahner — Thu, 14 May 2015 16:09:47 GMT

The problem has now been reproduced by SAS Global Support and a track has been opened.

Regards,

Jesper