BookmarkSubscribeRSS Feed
jespersahner
Calcite | Level 5

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

9 REPLIES 9
Kurt_Bremser
Super User

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.

PaulHomes
Rhodochrosite | Level 12

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

jespersahner
Calcite | Level 5

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

LinusH
Tourmaline | Level 20

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

Data never sleeps
jespersahner
Calcite | Level 5

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?

jespersahner
Calcite | Level 5

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

Regards,

Jesper

TomKari
Onyx | Level 15

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

jespersahner
Calcite | Level 5

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.

Ksharp
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 9 replies
  • 1976 views
  • 0 likes
  • 6 in conversation