/// <summary> /// 计算某时间段含有假期的天数 /// </summary> /// <param name="date">开始时间</param> /// <param name="date1">结束时间</param> /// <returns></returns> public int JSjqDay(DateTime date,DateTime date1) { int day = 0; //假期的天数 DateTime resultDate1 = date1; DateTime dateNow; string connStr = ConfigurationManager.AppSettings["ExFlowConnStr"]; using (SqlConnection conn = new SqlConnection(connStr)) { //type:1,节日 2,工作日 string SQL = "select date,type from exnewframe..ExHoliday where year >= " + date.Year.ToString(); SqlDataAdapter adp = new SqlDataAdapter(SQL, conn); DataTable dt = new DataTable(); adp.Fill(dt); dateNow = DateTime.Now; int n = (date - dateNow).Days; //resultTmp = GetNewDate2(date, dt, ref date1); try { //Response.Write("<script>alert(\"" + (DateTime.Compare(date, date1) <= 0 )+ "\");</script>"); while (DateTime.Compare(date, date1) <= 0) { if (date.DayOfWeek == DayOfWeek.Saturday || date.DayOfWeek == DayOfWeek.Sunday) //周末 { if (dt.Select("type=2 and date='" + date.ToString("yyyy-MM-dd 00:00:00.000") + "'").Length > 0) //在工作日中 { } else //不在工作日 { day++; } } else //不是周末 { //LogForYiDongBanGong.WriteLog(result.ToString("yyyy-MM-dd 00:00:00.000") + "||" + trnTime.ToString("yyyy-MM-dd") + "||" + "在节日表中"); if (dt.Select("type=1 and date='" + date.ToString("yyyy-MM-dd 00:00:00.000") + "'").Length > 0) //在节日表中 { day++; } else //不在节日表中 { } } date = date.AddDays(1); } } catch (Exception ex) { Response.Write("786:" + ex.Message); } } //Response.Write("<script>alert(\"" + date + "||" + date1 + "day" + day + "\");</script>"); return day; } 这样的话只能得到这个时间段中的假期日。 正如前面所说,节假日的这个问题是会动的,所以用这个是不能判断的... //addDays 计划的天数 //time7 不排除假期的目标天数 //time6 开始的日期 //LinShiDt 临时变量 //result 最终计算的目标天数 LinShiDt = time6.AddDays(addDays + JSjqDay(time6, time7)); result = LinShiDt.AddDays((JSjqDay(time6, LinShiDt))); DateTime dtLs = DateTime.MinValue; do { dtLs = result.AddDays(JSjqDay(LinShiDt.AddDays(1), result)); LinShiDt = result; result = dtLs; } while (JSjqDay(LinShiDt.AddDays(1), result) > 0);
看期间内包含了几个节假日,有一个就去掉24小时,就是一天;有两个就去掉两天。和时分秒没有关系。节假日都是按照整天(24小时0分0秒)计算的吧。--包含了几个节假日 select count(*) from 节假日表 where date between '工作开始日期' and '工作结束日期' --不带时分秒 开始时间:2010-6-10 12:00:00 处理时间: 2010-6-14 12:00:00 select count(*) from 节假日表 where date between '2010-6-10' and '2010-6-14' 2010-6-14 00:00:00 节假日( y) 2010-6-15 00:00:00 节假日( y) 2010-6-20 00:00:00 周末( y) 然后在计算一下第一天用了多少小时分秒,最后一天用了多少小时分秒。
dt.Select("type in (1,2) and date between '开始' and '结束' ) .length ;//这个就是包含了几个节假日
因为你的节假日都是不重叠的,因此只要从需要判断时间段中反复减掉节假日即可。例如判断时间段 [fr,t1] 与时间段 [f2,t2] 中间重叠了多长时间,可以写public static TimeSpan IntersectionOfDateTime(DateTime fr1, DateTime to1, DateTime fr2, DateTime to2) { if (fr1 > to2 || fr2 > to1) return TimeSpan.Zero; var fr = new DateTime(Math.Max(fr1.Ticks, fr2.Ticks)); var to = new DateTime(Math.Min(to1.Ticks, to2.Ticks)); return to.Subtract(fr); } 这是全部核心代码,仅此而已。现在要判断 2010-6-10 18:00:00 到 2010-6-20 10:00:00 之间相距多长时间,则可以写var f1 = DateTime.Parse("2010-6-10 18:00:00"); var t1 = DateTime.Parse("2010-6-20 10:00:00"); var result = t1.Subtract(f1); result -= IntersectionOfDateTime(f1, t1, DateTime.Parse("2010-6-14 00:00:00"), DateTime.Parse("2010-6-14 23:59:59.999")); result -= IntersectionOfDateTime(f1, t1, DateTime.Parse("2010-6-15 00:00:00"), DateTime.Parse("2010-6-15 23:59:59.999")); result -= IntersectionOfDateTime(f1, t1, DateTime.Parse("2010-6-20 00:00:00"), DateTime.Parse("2010-6-20 23:59:59.999"));这就是用 f1、t1 之间的总时间,减去分别与每一个节假日区间的相交时间(如果不想交就会是TimeSpan.Zero),最后得到结果就是“7天零6个小时”。
显然你可以看到var f1 = DateTime.Parse("2010-6-13 18:10:29"); var t1 = DateTime.Parse("2010-6-20 12:51:10"); 计算结果为“4天5小时49分31秒”
楼上说的对,如果只考虑天的话直接excel里面用networkdays,就可以了。 string sql = "select type as type,company as company,department as department, name as name,subject as subject,node_policy as node_policy,state as state,create_date as create_date,recieve_time as recieve_time,complete_time as complete_time from mystatis where" + where + " order by recieve_time"; DataTable dt = new DataTable(); SqlCommand cmd = new SqlCommand(sql, con); SqlDataAdapter Datapter = new SqlDataAdapter(cmd); Datapter.Fill(dt); string sql2 = "select h_start,h_end from myholidays order by h_start"; DataTable dt2 = new DataTable(); SqlCommand cmd2 = new SqlCommand(sql2, con); SqlDataAdapter dpt = new SqlDataAdapter(cmd2); dpt.Fill(dt2);
for(int i=0;i<dt.Rows.Count;i++) { DataRow dr = dt.Rows.Find(dt2); if(dr!=null){ var f1 = DateTime.Parse(dt.Rows[i]["receive_time"].ToString()); var t1 = DateTime.Parse(dt.Rows[i]["complete_time"].ToString()); var result = t1.Subtract(f1); result -= IntersectionOfDateTime(f1, t1, DateTime.Parse(dt2.Rows[i]["h_start"].ToString()), DateTime.Parse(dt2.Rows[i]["h_end"].ToString())); } else{ var f1 = DateTime.Parse(dt.Rows[i]["receive_time"].ToString()); var t1 = DateTime.Parse(dt.Rows[i]["complete_time"].ToString()); var result = t1-f1; } }
/// <summary>
/// 计算某时间段含有假期的天数
/// </summary>
/// <param name="date">开始时间</param>
/// <param name="date1">结束时间</param>
/// <returns></returns>
public int JSjqDay(DateTime date,DateTime date1)
{
int day = 0; //假期的天数
DateTime resultDate1 = date1;
DateTime dateNow;
string connStr = ConfigurationManager.AppSettings["ExFlowConnStr"];
using (SqlConnection conn = new SqlConnection(connStr))
{
//type:1,节日 2,工作日
string SQL = "select date,type from exnewframe..ExHoliday where year >= " + date.Year.ToString();
SqlDataAdapter adp = new SqlDataAdapter(SQL, conn);
DataTable dt = new DataTable();
adp.Fill(dt);
dateNow = DateTime.Now;
int n = (date - dateNow).Days;
//resultTmp = GetNewDate2(date, dt, ref date1);
try
{
//Response.Write("<script>alert(\"" + (DateTime.Compare(date, date1) <= 0 )+ "\");</script>");
while (DateTime.Compare(date, date1) <= 0)
{
if (date.DayOfWeek == DayOfWeek.Saturday || date.DayOfWeek == DayOfWeek.Sunday) //周末
{
if (dt.Select("type=2 and date='" + date.ToString("yyyy-MM-dd 00:00:00.000") + "'").Length > 0) //在工作日中
{ }
else //不在工作日
{
day++;
}
}
else //不是周末
{
//LogForYiDongBanGong.WriteLog(result.ToString("yyyy-MM-dd 00:00:00.000") + "||" + trnTime.ToString("yyyy-MM-dd") + "||" + "在节日表中");
if (dt.Select("type=1 and date='" + date.ToString("yyyy-MM-dd 00:00:00.000") + "'").Length > 0) //在节日表中
{
day++;
}
else //不在节日表中
{
}
}
date = date.AddDays(1);
}
}
catch (Exception ex)
{
Response.Write("786:" + ex.Message);
}
} //Response.Write("<script>alert(\"" + date + "||" + date1 + "day" + day + "\");</script>");
return day;
}
这样的话只能得到这个时间段中的假期日。
正如前面所说,节假日的这个问题是会动的,所以用这个是不能判断的...
//addDays 计划的天数
//time7 不排除假期的目标天数
//time6 开始的日期
//LinShiDt 临时变量
//result 最终计算的目标天数
LinShiDt = time6.AddDays(addDays + JSjqDay(time6, time7));
result = LinShiDt.AddDays((JSjqDay(time6, LinShiDt)));
DateTime dtLs = DateTime.MinValue;
do
{
dtLs = result.AddDays(JSjqDay(LinShiDt.AddDays(1), result));
LinShiDt = result;
result = dtLs;
}
while (JSjqDay(LinShiDt.AddDays(1), result) > 0);
select count(*) from 节假日表 where date between '工作开始日期' and '工作结束日期' --不带时分秒
开始时间:2010-6-10 12:00:00 处理时间: 2010-6-14 12:00:00 select count(*) from 节假日表 where date between '2010-6-10' and '2010-6-14'
2010-6-14 00:00:00 节假日( y)
2010-6-15 00:00:00 节假日( y)
2010-6-20 00:00:00 周末( y)
然后在计算一下第一天用了多少小时分秒,最后一天用了多少小时分秒。
{
if (fr1 > to2 || fr2 > to1)
return TimeSpan.Zero; var fr = new DateTime(Math.Max(fr1.Ticks, fr2.Ticks));
var to = new DateTime(Math.Min(to1.Ticks, to2.Ticks));
return to.Subtract(fr);
}
这是全部核心代码,仅此而已。现在要判断 2010-6-10 18:00:00 到 2010-6-20 10:00:00 之间相距多长时间,则可以写var f1 = DateTime.Parse("2010-6-10 18:00:00");
var t1 = DateTime.Parse("2010-6-20 10:00:00");
var result = t1.Subtract(f1);
result -= IntersectionOfDateTime(f1, t1, DateTime.Parse("2010-6-14 00:00:00"), DateTime.Parse("2010-6-14 23:59:59.999"));
result -= IntersectionOfDateTime(f1, t1, DateTime.Parse("2010-6-15 00:00:00"), DateTime.Parse("2010-6-15 23:59:59.999"));
result -= IntersectionOfDateTime(f1, t1, DateTime.Parse("2010-6-20 00:00:00"), DateTime.Parse("2010-6-20 23:59:59.999"));这就是用 f1、t1 之间的总时间,减去分别与每一个节假日区间的相交时间(如果不想交就会是TimeSpan.Zero),最后得到结果就是“7天零6个小时”。
var t1 = DateTime.Parse("2010-6-20 12:51:10");
计算结果为“4天5小时49分31秒”
2. S= F(T1) + (List<D> - T1 -T2 ) + F(T2)
3. F(T1)= 如果List<D>中包含T1,则返回从T1到T1下一天0点的时间间隔, 如果不包含T1,则返回0
2. F(T2)=如果 List<D>中包含T2, 则返回从T2的0点到T2的时间间隔,如果不包含则返回0
string sql = "select type as type,company as company,department as department, name as name,subject as subject,node_policy as node_policy,state as state,create_date as create_date,recieve_time as recieve_time,complete_time as complete_time from mystatis where" + where + " order by recieve_time";
DataTable dt = new DataTable();
SqlCommand cmd = new SqlCommand(sql, con);
SqlDataAdapter Datapter = new SqlDataAdapter(cmd);
Datapter.Fill(dt); string sql2 = "select h_start,h_end from myholidays order by h_start";
DataTable dt2 = new DataTable();
SqlCommand cmd2 = new SqlCommand(sql2, con);
SqlDataAdapter dpt = new SqlDataAdapter(cmd2);
dpt.Fill(dt2);
for(int i=0;i<dt.Rows.Count;i++)
{
DataRow dr = dt.Rows.Find(dt2);
if(dr!=null){
var f1 = DateTime.Parse(dt.Rows[i]["receive_time"].ToString());
var t1 = DateTime.Parse(dt.Rows[i]["complete_time"].ToString());
var result = t1.Subtract(f1);
result -= IntersectionOfDateTime(f1, t1, DateTime.Parse(dt2.Rows[i]["h_start"].ToString()), DateTime.Parse(dt2.Rows[i]["h_end"].ToString()));
}
else{
var f1 = DateTime.Parse(dt.Rows[i]["receive_time"].ToString());
var t1 = DateTime.Parse(dt.Rows[i]["complete_time"].ToString());
var result = t1-f1;
}
}