62,046
社区成员
发帖
与我相关
我的任务
分享
#region 计算开始日期到结束日期的天数
/// <summary>
/// 计算开始日期到结束日期的天数(除去周六周天)
/// </summary>
/// <param name="beginTime"></param>
/// <param name="endTime"></param>
/// <returns></returns>
private int Compute(string beginTime, string endTime)
{
DateTime bTime = DateTime.Parse(beginTime);
DateTime eTime = DateTime.Parse(endTime);
string[] arrStr = { "sunday", "saturday" };
int sumDay = ((TimeSpan)(eTime - bTime)).Days;//记录计划开始日期跟结束日期间总的天数;
int dateCount = 0; //用于记录消除星期天和星期六后的实际工作天数;
int count = 0; //用于记录星期日和星期六的天数;
for (int i = 0; i < sumDay; i++)
{
for (int j = 0; j < arrStr.Length; j++)
{
if (bTime.AddDays(i).DayOfWeek.ToString().ToLower() == arrStr[j])
{
count++;
break;
}
}
}
dateCount = sumDay - count;
return dateCount;
}
#endregion
--计算两个日期相差的工作天数
CREATE FUNCTION f_WorkDay(
@dt_begin datetime, --计算的开始日期
@dt_end datetime --计算的结束日期
)RETURNS int
AS
BEGIN
DECLARE @workday int,@i int,@bz bit,@dt datetime
IF @dt_begin>@dt_end
SELECT @bz=1,@dt=@dt_begin,@dt_begin=@dt_end,@dt_end=@dt
ELSE
SET @bz=0
SELECT @i=DATEDIFF(Day,@dt_begin,@dt_end)+1,
@workday=@i/7*5,
@dt_begin=DATEADD(Day,@i/7*7,@dt_begin)
WHILE @dt_begin<=@dt_end
BEGIN
SELECT @workday=CASE
WHEN (@@DATEFIRST+DATEPART(Weekday,@dt_begin)-1)%7 BETWEEN 1 AND 5
THEN @workday+1 ELSE @workday END,
@dt_begin=@dt_begin+1
END
RETURN(CASE WHEN @bz=1 THEN -@workday ELSE @workday END)
END
GO