3 Replies Latest reply: Oct 20, 2006 11:17 AM by 796447 RSS

    Adding days to a calendar date

    807598
      Hi, I have to insert pay periods in a database and need to be able to have a start and end date for each record. To add to the complexity and I have Exempt and Non-Exempt employees with different settings (that is not so important here). An exempt employee enters thier time in 2 week increments. So, as you will see in the code below, if I start on January 3rd of 2000 my start date will be incremented by 13 for each loop though the years and weeks. This works great! However, as soon as I start trying to set my endDate it doesn't work.

      Here's what happens. It starts out looking great where my endDate increment is 1 day shorted than the start date (to avoid overlapping). Then toward the bottom of my loop (60 years time 26 weeks) My pay period has a 2+ years span?? Makes no sense to me at all.
          public String loadPayPeriods(HttpServletRequest request,int isExempt) throws SQLException,Exception{
              StringBuffer query=new StringBuffer(500);
              
              int startYear=2000;
              int numYears=2060;
              int startWeek=1;
              int numWeeks=0;
              int increment=0,endIncrement=0;
              int exemptInt;
              Calendar startDate=Calendar.getInstance();
              Calendar endDate=Calendar.getInstance();
              String DATE_FORMAT = "yyyy-MM-dd";
              String YEAR_FORMAT = "yyyy";
              String MONTH_FORMAT = "MM";
              String DAY_FORMAT = "dd";
              java.text.SimpleDateFormat sdf=new java.text.SimpleDateFormat(DATE_FORMAT);
              
              if (isExempt==1){
                  startDate.set(2000,0,3);
                  endDate.set(2000,0,3);
                  numWeeks=26;
                  increment=14;
                  exemptInt=1;
              } else {
                  startDate.set(2006,0,1);
                  endDate.set(2006,0,1);
                  numWeeks=52;
                  increment=7;
                  exemptInt=0;
              }
              
              // the end increment is one day less then the start increment so set that here
              endIncrement=increment-1;
              
              // setup the session for error handling
              HttpSession session=request.getSession(false);
              
              query.append(endIncrement);
              query.append("<br><br>");
              
              // loop through the number of years that we are inserting
              for(startYear=2000;startYear<=numYears;startYear++){
                  
                  // loop over the number of weeks in a year
                  for(startWeek=1;startWeek<=numWeeks;startWeek++){
                      
                      // set the start and end dates here
                      if (startWeek != 1){
                          startDate.add(startDate.DATE,increment);
                          // add the one first otherwise we tumble out of control by
                          // always being behind by one, unless this is the first week
                          endIncrement=increment;
                      }
                      
                      // increment the end date by the number of days in the payperiod
                      // minus one.
                      endDate.add(startDate.DATE,endIncrement);
                      
                      query.append("<pre>");
                      query.append("INSERT INTO Pay_Period_Table(");
                      query.append("Pay_Period_ID, ");
                      query.append("Pay_Period_Start, ");
                      query.append("Pay_Period_End, ");
                      query.append("Is_Active, ");
                      query.append("Is_Deleted, ");
                      query.append("Is_Exempt ");
                      query.append(") ");
                      query.append("VALUES ( ");
                      query.append("SYS_GUID(), ");
                      query.append("to_date('"+sdf.format(startDate.getTime())+"'), ");
                      query.append("to_date('"+sdf.format(endDate.getTime())+"'), ");
                      query.append("1, ");
                      query.append("0, ");
                      query.append(exemptInt);
                      query.append(")");
                      query.append("</pre>");
                      
                      query.append("<br><br>");
                      /*
                      try {
                          // get the connection
                          Connection dbCon=new DbConnection().getConnection();
                          
                          // Create a Statement
                          Statement stmt = dbCon.createStatement();
                          
                          // set the timeout here so that we don't get overlapping statements
                          stmt.setQueryTimeout(2);
                          
                          // run the update/insert
                          stmt.executeUpdate(query.toString());
                                              
                          stmt.close();
                          dbCon.commit();
                          dbCon.close();
                      
                      } catch (SQLException se){
                          session.setAttribute("errMessage","SQL Error: "+se);
                      } catch (Exception e){
                          session.setAttribute("errMessage","General Exception: "+e);
                      } finally {
                          // TODO: Finally statement
                      }
                      */
                      
                  }
                  
              }
              
              return query.toString();
              
          }
      If anyone has any ideas I would LOVE to see what I am doing wrong. It baffels me and has been for several hours.

      Thanks!

      -- Nik
        • 1. Re: Adding days to a calendar date
          807598
          Just giving a quick glance, I believe the problem is this line:
           endDate.add(startDate.DATE,endIncrement);
          If you are trying to say the endDate should be the startDate + endIncrement, the code should read:
          endDate.setTime(startDate.getTime());
          endDate.add(Calendar.DATE, endIncrement);
          DATE is a static, final field in Calendar that just tells add() which value to increment (static fields should be referenced through the class name rather than by an instance name).

          Let me know if I misunderstood what you were trying to do.
          • 2. Re: Adding days to a calendar date
            796447
            Why are you independently incrementing both startDate and endDate by a certain number of days? And you're only incrementing startDate when startWeek != 1, so you start drifting.

            Should not endDate ALWAYS be increment-1 days from startDate?
            Just increment startDate, and when you want to recalibrate endDate, just do it by:
            1) set endDate's date to the same as startDate
            2) then add increment-1 days to that
            • 3. Re: Adding days to a calendar date
              807598
              You are the man! (...or woman)

              Thanks, that is exactly what I was looking for. I didn't get how to set endDate to the value of startDate.

              Thanks!