11 Replies Latest reply: Nov 17, 2006 4:57 AM by 807599 RSS

    Getting date and time from microsoft date

    807599
      Hi all,

      i have a problem converting microsoft date into java date and time:
      protected Date nomDateToDate(long nomDate) {
      
                int y = 1900, m = 0, d = 1;
                
                GregorianCalendar c_1900 = new GregorianCalendar();
                c_1900.set(y, m, d);
                
                long time = nomDate * 24 * 60 * 60 * 1000 + c_1900.getTimeInMillis();
                          
                GregorianCalendar cal = new GregorianCalendar();
                cal.setTimeInMillis(time);
                cal.add(GregorianCalendar.DATE, -2);
      
                return cal.getTime();
           }
      I call this method like:
      nomDateToDate(55757940 / 24 / 60);

      This gets the right date but not the time because the parameter is of kind long. How to get date and time for the given microsoft date (55757940)? Do i need a float value? How to convert that into time value?

      Thanks for any help,

      best regards,

      ak
        • 1. Re: Getting date and time from microsoft date
          807599
          Define: "Microsoft Date".
          • 2. Re: Getting date and time from microsoft date
            807599
            Hi,
            sorry for that. I mean microfosoft date when talking about minutes from 01.01.1900.
            So when i use the parameter / 24 / 60 i got days from 01.01.1900 but no hours because its a long value...

            And yes i did search the forums but still got no answer...

            Message was edited by:
            barnti
            • 3. Re: Getting date and time from microsoft date
              807599
              If the date is expressed as minutes from 01.01.1900 GMT, then you can use a calendar as follows:
              protected Date nomDateToDate(long nomDate) {
              
                      // Set-up Microsoft's "epoch"
                      Calendar c = Calendar.getInstance();
                      c.setTimeZone(TimeZone.getTimeZone("GMT"));
                      c.set(Calendar.YEAR, 1900);
                      c.set(Calendar.MONTH, Calendar.JANUARY);
                      c.set(Calendar.DAY_OF_YEAR, 1);
                      c.clear();
              
                      // Compute date by add given minutes
                      c.add(Calendar.MINUTE, nomDate);
              
                      return c.getTime();
              }
              • 4. Re: Getting date and time from microsoft date
                807599
                Hi Tim,

                yes, that is what i do in my code as shown above. Now the point is i get the right date + 2 days + 9 hours + 30 minutes.
                Where is this offset from?
                • 5. Re: Getting date and time from microsoft date
                  807599
                  Hi again,

                  what i got is actual time plus 9 hours. And nomDate is of long value not int...
                  • 6. Re: Getting date and time from microsoft date
                    807599
                    When you set the year/month/day with the set(int, int, int) method, previous values of other fields are retained (i.e. current time.)
                    You should call clear first.
                    Also, beware that when you set calendar's fields, they are taken into account in the context of current time zone of the calendar.
                    Finally, you don't need another Calendar to build the resulting Date. Simply use the Date(long) constructor.
                    • 7. Re: Getting date and time from microsoft date
                      807599
                      Hi Tim,

                      ok understood! Now i got the following:
                      protected Date nomDateToDate(long nomDate) {
                                int y = 1900, m = 0, d = 1;
                                
                                cal = new GregorianCalendar();
                                cal.setTimeZone(TimeZone.getTimeZone("CET"));
                                cal.set(y,m,d);
                                cal.clear();
                                
                                
                                long time = nomDate / (60 * 1000); //to get in minutes
                                
                                cal.add(Calendar.MINUTE, (int) time);
                                
                                          
                                cal.add(GregorianCalendar.DATE, -2);
                                //cal.add(GregorianCalendar.HOUR_OF_DAY, -9);
                                //cal.add(GregorianCalendar.MINUTE, -59);
                      
                                return cal.getTime();
                           }
                      So this returns still the right date (2 days offset! )but with wrong time. Still i get actual time + 9 hours...
                      What am i doing wrong?

                      Best regards,

                      ak
                      • 8. Re: Getting date and time from microsoft date
                        807599
                        My adding minutes approach was wrong, as it may overflow due to int parameter type.
                        Using epoch's time in milliseconds and building a Date directly seems to be a better approach:
                            protected Date getDate(long minutesFrom1900) {
                        
                                // Set-up Microsoft's "epoch"
                                Calendar c = Calendar.getInstance();
                                c.setTimeZone(TimeZone.getTimeZone("UTC")); // or whatever Microsoft's epoch timezone is, 
                                                                            // but I guess/hope it uses UTC (or GMT)
                                c.set(Calendar.YEAR, 1900);
                                c.set(Calendar.MONTH, Calendar.JANUARY);
                                c.set(Calendar.DAY_OF_YEAR, 1);
                                c.clear();
                                
                                // Obtain required millisecond values
                                long epoch1900      = c.getTimeInMillis();
                                long millisFrom1900 = 60000LL * minutesFrom1900;
                                
                                // Create corresponding Date instance
                                return new Date(epoch1900 + millisFrom1900);
                            }
                        • 9. Re: Getting date and time from microsoft date
                          807599
                          Hi Tim,

                          YES! That was the right way! Thanks for that. Working with dates and times seems to be a little bit tricky every time. Now i get the right time!

                          One thing: Why do i get 2 days offset? For date i get 05.01.2006 but it should be 03.01.2006. Any idea for that?
                          • 10. Re: Getting date and time from microsoft date
                            807599
                            One thing: Why do i get 2 days offset? For date i get
                            05.01.2006 but it should be 03.01.2006. Any idea for
                            that?
                            Note that if you're talking about the value 55757940, it should represent year 2076, not 2006.
                            Anyway, a difference of two days is a lot. The only thing I can think of is that the two systems have different calendars, leap days or something.
                            I guess you have to investigate further...
                            Good luck,
                            Tim.
                            • 11. Re: Getting date and time from microsoft date
                              807599
                              Hi Tim,

                              yes, thats why: Microsoft date is since 1900 java calculates with time from 1970...

                              Thanks a lot,
                              best regards,

                              ak