dateformat + java

kshkid · 07-27-2008, 03:58 AM

Hi all,

Am ensuring that before posting I tried searching the forum but it seems that there had been no such similar issues before.

Am using Jakarta - POI for reading excel file using Java

Following are the libraries that am using

poi-3.0-rc4-20070503.jar
poi-contrib-3.0-rc4-20070503.jar
poi-scratchpad-3.0-rc4-20070503.jar

There is a strange problem when reading date values from excel, date values are interpreted in numerical format rather than date format. ( For ex: 01/01/1900 is interpreted as value 2 )

Is this how, this will work ? ( Am not sure )
So, I wrote a converter to change numbers to date format

Code:

float val = Float.parseFloat(temp);
long milliSeconds = (long) val;
SimpleDateFormat dateFormat = new SimpleDateFormat("MM/dd/yy");
Date resultDate = new Date(milliSeconds);
dateFormat.format(resultDate);
return dateFormat.toString();

But this doesn't work and returns "01/01/1970" always.

Could you please provide some pointers ?

Thanks a lot

vladmihaisima · 07-27-2008, 07:21 AM

dateFormat.format(resultDate) returns a string with the formatted date. So you should try "return dateFormat.format(resultDate);"

kshkid · 07-27-2008, 08:09 AM

thanks for the reply,

but it still returns only "01/01/1970" and nothing else whatever be the value

Here is the code snippet

Code:

recv_arg = 39723.0
DEF_DATE = MM/dd/yyyy

Code:

float value = Float.parseFloat(recv_arg);
long milliSec = (long) value;
SimpleDateFormat dateFormatSrc = new SimpleDateFormat(DEF_DATE);
Date resultDate = new Date(milliSec);
return dateFormatSrc.format(resultDate);

vladmihaisima · 07-27-2008, 09:35 AM

The value is in milliSec (the variable is named the same in your code). 39723 means 39 seconds from the start of time (which for java is 01/01/1970).

If you want to see something different try for example with "39723000000.0".

I do not know what is the unit of the string you get from excel... (might be seconds, hours, days, etc).

kshkid · 07-28-2008, 12:10 AM

I did a manual calculation and it seems the unit is in days.

How can I specify days as the number of units in SimpleDateFormat ?

paulsm4 · 07-28-2008, 12:39 AM

Please step back a bit...

1. "1/1/1970" means you've got a date value of "0".

2. This means that your long variable "milliSec" is probably zero.

3. Which, in turn, means that you might not even have initialized "float val" correctly.

SUGGESTIONS:
1. Double check if the "millSec" argument in "new Date(milliSec)" should even be a "long" (I don't recall. I think it's right, but it's worth double-checking).

2. Step through your code under a debugger (or use "System.out.println()"), and make sure "val", "milliSec" and your other variables are even being initialized correctly.

'Hope that helps .. PSM

vladmihaisima · 07-28-2008, 03:36 AM

@paulsm32: there is nothing wrong with the code. The date "1/1/1970" covers a lot of values in miliseconds (including 39723).

@kshkid: you do not specify the unit, you transfor the days in miliseconds by multypling with 1000*60*60*24. (*24 to get hours, *60 to get minutes, *60 to get seconds, *1000 to get miliseconds). Something like:

Code:

...
long milliSec = (long) value;
milliSec *= 1000*60*60*24;
...

kshkid · 07-28-2008, 10:03 PM

Sorry, for the confusion caused.

Actually they are not milliSeconds, they are the number of days since Jan 1, 1970.

I did a manual breakdown and it gives me the correct result since 01/01/1970.

Is there any function in Java, given the number of days that would return date since 01/01/1970 ? Something like if the number of days is 13, it should return 13/01/1970

thanks again

kshkid · 07-28-2008, 10:39 PM

I tried the following code and something strange is going on ( as far as I can see )

The value fetched from excel cell is 39724 which I would like to translate it as 10/03/2008 ( Oct 3, 2008 ), but its displaying some other date value like 2079 ( year )

Code:

public static void main(String[] args) {
  long numDays =14155;
  numDays *= (60 * 60  *24 * 1000);
  Date date2 = new Date(numDays);
  DateFormat dataformat =  DateFormat.getDateInstance(DateFormat.LONG);     
  System.out.println(dataformat.format(date2));
  int val = 39724 - 14155;
  System.out.println("this:" + val);
}

I have to explicitly subtract a value of 25569 to get the expected result.

Any idea, whats happening here ?

vladmihaisima · 07-29-2008, 02:43 AM

You are not correct. You said: "they are the number of days since Jan 1, 1970".

If I do a simple calculation 39724 / 365 = 108. So the year should be 1970 + 108 = 2078, as Java is telling you.

My guess is that your value is the number of days since "Jan 1, 1900" so you have to substract from that one the number of days between 1970 and 1900 = 70 * 365 = 25550. So that's why you have to 'substract' that value. I am not saying your value (25569) is completly correct, please check it in the light of what I said.

kshkid · 07-29-2008, 10:09 PM

You are right. Its number of days since Jan 1, 1900 and the value 25569 ( number of days from Jan 1, 1900 to Jan 1, 1970 <not inclusive> ) is correct

Here is the code that I came up with - given a number it should return date in numerical format

Code:

float val = Float.parseFloat(temp);
long numberOfDays = (long) val;
numberOfDays -= 25569;
numberOfDays *= ( 60 * 60 * 24 * 1000 );
SimpleDateFormat dateFormat = new SimpleDateFormat(DEFAULT_DATE_FORMAT);
Date resultDate = new Date(numberOfDays);
return dateFormat.format(resultDate);

Thanks a lot for your help!