java或者oracle处理请假时间半天的计算方法

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.TimeZone;public class MyDate {
public static void main(String[] args) throws ParseException {
String starttime="2011-08-15 10:30:00";
String endtime="2011-08-19 10:30:00";
System.out.println(1.50/8.00);
double b =new MyDate().jisuan(starttime, endtime);
System.out.println(b);
}
public double jisuan(String s1 ,String s2) throws ParseException{
SimpleDateFormat s = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Date d1 = s.parse(s1);
Date d2 = s.parse(s2);
SimpleDateFormat ss = new SimpleDateFormat("yyyy-MM-dd");
Date dd1 = ss.parse(s1);
Date dd11 = ss.parse(s1);
Date dd2 = ss.parse(s2);
System.out.println(dd1);
Calendar ca = Calendar.getInstance();
if(dd1.getTime()==dd2.getTime()){//同一天
if(this.getweekdayloc(dd1,ca)==0){//星期天
return 0;
}else{
return this.getshijian(d2,ca)-(1-this.getshijian(d1,ca));
}
}else{
double bb=0.000;
while(dd2.getTime()>=dd1.getTime()){
if(this.getweekdayloc(dd1,ca)==1){
if(dd1.getTime()==dd11.getTime()){
bb+=this.getshijian(d1,ca);
}else if(dd1.getTime()==dd2.getTime()){
bb+=(1-this.getshijian(d2,ca));
}else{
bb+=1;
}
}

ca.setTime(d1);
ca.add(Calendar.DATE, 1);
d1=ca.getTime();
ca.clear();
ca.setTime(dd1);
ca.add(Calendar.DATE, 1);
dd1=ca.getTime();
System.out.println(bb);
}

return bb;
}

}

//传进一个日期判断是否是周六、日。六日返回0，其他返回1
private int getweekdayloc(Date date,Calendar ca){
ca.setTime(date);
if(ca.get(Calendar.DAY_OF_WEEK)==1 || ca.get(Calendar.DAY_OF_WEEK)==7){
return 0;
}else{
return 1;
}

}
//判断
private double getshijian(Date date,Calendar ca){
ca.setTime(date);
int shi = ca.get(Calendar.HOUR_OF_DAY);
int fen = ca.get(Calendar.MINUTE);
double d = shi+fen/60.000;
if(shi<9.000){
return 1.000;
}else if(d<=12.500 && d>=9.000){
return (12.500-d+(18.000-13.500))/8;
}else if(d>12.500 && d<13.000){
return (18.000-13.500)/8;
}else if(d>=13.500&& d<=18.000){
return (18.000-d)/8;
}else{
return 0.000;
}
}}可能和你需求有一点不符合，修改下getshijian方法就Ok

scott@ORCL> desc t_kaoqin
名称                                                  是否为空? 类型
----------------------------------------------------- -------- -------------------------------- ID                                                    NOT NULL NUMBER
STARTDATE                                                      DATE
ENDDATE                                                        DATEscott@ORCL> select id ,
  2  to_char(startdate,'yyyy-MM-dd hh24:mi:ss'),
  3  to_char(enddate,'yyyy-MM-dd hh24:mi:ss')
  4   from t_kaoqin;        ID TO_CHAR(STARTDATE,' TO_CHAR(ENDDATE,'YY
---------- ------------------- -------------------
         1 2011-08-12 16:30:00 2011-08-17 16:45:00
         2 2011-08-12 16:30:00 2011-08-17 10:30:00
         3 2011-08-02 16:30:00 2011-08-17 16:30:00scott@ORCL> select id,
  2         case
  3           when eTime - sTime < 7 then
  4            eTime - sTime - decode(sign(to_number(to_char(eTime, 'D')) -
  5                                        to_number(to_char(sTime, 'D'))),
  6                                   1,
  7                                   0,
  8                                   2)
  9           else
10            eTime - sTime - (decode(sign(to_number(to_char(eTime, 'D')) -
11                                        to_number(to_char(sTime, 'D'))),
12                                   1,
13                                   0,
14                                   2) + trunc((eTime - sTime) / 7) * 2)
15         end days
16    from (select id,
17                 to_date(to_char(startdate, 'yyyy-MM-dd') || ' 00:00:00',
18                         'yyyy-MM-dd hh24:mi:ss') +
19                 decode(sign(startdate -
20                             to_date(to_char(startdate, 'yyyy-MM-dd') ||
21                                     ' 12:30:00',
22                                     'yyyy-MM-dd hh24:mi:ss')),
23                        -1,
24                        0,
25                        0.5) sTime,
26                 to_date(to_char(enddate, 'yyyy-MM-dd') || ' 00:00:00',
27                         'yyyy-MM-dd hh24:mi:ss') +
28                 decode(sign(enddate -
29                             to_date(to_char(enddate, 'yyyy-MM-dd') ||
30                                     ' 12:30:00',
31                                     'yyyy-MM-dd hh24:mi:ss')),
32                        -1,
33                        0.5,
34                        1) eTime
35            from t_kaoqin);        ID       DAYS
---------- ----------
         1        3.5
         2          3
         3       11.5给你写了一个，你可以参考一下

修改下 getshijian 方法就可以了！
不到半天的返回0.5
超过半天小于一天返回1

感觉lz需求不完整，
要是节假日怎么办？
lz应该建立一个假期表,用来维护假期！
凡是请假日期在假期表内都不算请假！

没有必要用存储过程，你还是从java程序中实现比较好
但给你写了一个存储过程demo，有兴趣可以参考一下吧create or replace procedure p_kaoqin is  type jjr_type is table of jjr_t.time%type;
  jjr_tab jjr_type;
  type c_ref_type is ref cursor;
  myCur c_ref_type;
  type rec_type is record(
    id    t_kaoqin.id%type,
    sTime t_kaoqin.startdate%type,
    eTime t_kaoqin.enddate%type,
    days  number);
  rec rec_type;
begin
  select time bulk collect into jjr_tab from jjr_t;
  open myCur for q'!select id,sTime,eTime,
       case
         when eTime - sTime < 7 then
          eTime - sTime - decode(sign(to_number(to_char(eTime, 'D')) -
                                      to_number(to_char(sTime, 'D'))),
                                 1,
                                 0,
                                 2)
         else
          eTime - sTime - (decode(sign(to_number(to_char(eTime, 'D')) -
                                      to_number(to_char(sTime, 'D'))),
                                 1,
                                 0,
                                 2) + trunc((eTime - sTime) / 7) * 2)
       end days
  from (select id,
               to_date(to_char(startdate, 'yyyy-MM-dd') || ' 00:00:00',
                       'yyyy-MM-dd hh24:mi:ss') +
               decode(sign(startdate -
                           to_date(to_char(startdate, 'yyyy-MM-dd') ||
                                   ' 12:30:00',
                                   'yyyy-MM-dd hh24:mi:ss')),
                      -1,
                      0,
                      0.5) sTime,
               to_date(to_char(enddate, 'yyyy-MM-dd') || ' 00:00:00',
                       'yyyy-MM-dd hh24:mi:ss') +
               decode(sign(enddate -
                           to_date(to_char(enddate, 'yyyy-MM-dd') ||
                                   ' 12:30:00',
                                   'yyyy-MM-dd hh24:mi:ss')),
                      -1,
                      0.5,
                      1) eTime
          from t_kaoqin)!';
  loop
    fetch myCur
      into rec;
    exit when myCur%notfound;
    for i in jjr_tab.FIRST .. jjr_tab.LAST loop
      if jjr_tab(i) between rec.sTime and rec.eTime then
        rec.days := rec.days - 1;
      end if;
    end loop;
    DBMS_OUTPUT.put_line(rec.id ||'请假天数为 : ' || rec.days);
  end loop;
  close myCur;
exception
  when others then
    DBMS_OUTPUT.put_line('error');
    raise;
end p_kaoqin;测试-- Created on 2011-8-31 by ADMINISTRATOR
declare
  -- Local variables here
  i integer;
begin
  -- Test statements here
  p_kaoqin();
end;1请假天数为 : 2.5
2请假天数为 : 2
3请假天数为 : 9.5SQL> select * from t_kaoqin;

        ID STARTDATE   ENDDATE
---------- ----------- -----------
         1 2011-8-12 1 2011-8-17 1
         2 2011-8-12 1 2011-8-17 1
         3 2011-8-2 16 2011-8-17 1SQL> select * from jjr_t;

TIME
-----------
2011-8-16
2011-8-9

这与实际不符合吧，比如今年的国庆节假日是10.1-10.7日就有周末了，假日表里面难不成我不存10.1号和10.2号了呀，不过我用程序还是实现了，oracle不是很懂，就没用sql去处理了，谢谢！！！

调试易

java或者oracle处理请假时间半天的计算方法

解决方案 »