62,046
社区成员
发帖
与我相关
我的任务
分享
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_weekdaycount]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_weekdaycount]
GO
/**//*--计算任意两个时间之间的星期几的次数(横向显示)
本方法直接判断 @@datefirst 做对应处理
不受 sp_language 及 set datefirst 的影响
--邹建 2004.08(引用请保留此信息)--*/
/**//*--调用示例
select * from f_weekdaycount('2004-9-01','2004-9-02')
--*/
create function f_weekdaycount(
@dt_begin datetime,
@dt_end datetime
)returns table
as
return(
select 跨周数
,周一=case a
when -1 then case when 1 between b and c then 1 else 0 end
when 0 then case when b<=1 then 1 else 0 end
+case when c>=1 then 1 else 0 end
else a+case when b<=1 then 1 else 0 end
+case when c>=1 then 1 else 0 end
end
,周二=case a
when -1 then case when 2 between b and c then 1 else 0 end
when 0 then case when b<=2 then 1 else 0 end
+case when c>=2 then 1 else 0 end
else a+case when b<=2 then 1 else 0 end
+case when c>=2 then 1 else 0 end
end
,周三=case a
when -1 then case when 3 between b and c then 1 else 0 end
when 0 then case when b<=3 then 1 else 0 end
+case when c>=3 then 1 else 0 end
else a+case when b<=3 then 1 else 0 end
+case when c>=3 then 1 else 0 end
end
,周四=case a
when -1 then case when 4 between b and c then 1 else 0 end
when 0 then case when b<=4 then 1 else 0 end
+case when c>=4 then 1 else 0 end
else a+case when b<=4 then 1 else 0 end
+case when c>=4 then 1 else 0 end
end
,周五=case a
when -1 then case when 5 between b and c then 1 else 0 end
when 0 then case when b<=5 then 1 else 0 end
+case when c>=5 then 1 else 0 end
else a+case when b<=5 then 1 else 0 end
+case when c>=5 then 1 else 0 end
end
,周六=case a
when -1 then case when 6 between b and c then 1 else 0 end
when 0 then case when b<=6 then 1 else 0 end
+case when c>=6 then 1 else 0 end
else a+case when b<=6 then 1 else 0 end
+case when c>=6 then 1 else 0 end
end
,周日=case a
when -1 then case when 0 between b and c then 1 else 0 end
when 0 then case when b<=0 then 1 else 0 end
+case when c>=0 then 1 else 0 end
else a+case when b<=0 then 1 else 0 end
+case when c>=0 then 1 else 0 end
end
from(
select 跨周数=case when @dt_begin<@dt_end
then (datediff(day,@dt_begin,@dt_end)+7)/7
else (datediff(day,@dt_end,@dt_begin)+7)/7 end
,a=case when @dt_begin<@dt_end
then datediff(week,@dt_begin,@dt_end)-1
else datediff(week,@dt_end,@dt_begin)-1 end
,b=case when @dt_begin<@dt_end
then (@@datefirst+datepart(weekday,@dt_begin)-1)%7
else (@@datefirst+datepart(weekday,@dt_end)-1)%7 end
,c=case when @dt_begin<@dt_end
then (@@datefirst+datepart(weekday,@dt_end)-1)%7
else (@@datefirst+datepart(weekday,@dt_begin)-1)%7 end)a
)
go
using System;
using System.Collections.Generic;
using System.Text;
using System.Drawing;
using System.Drawing.Imaging;
using System.Windows.Forms;
using System.Reflection;
using System.Collections;
namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{
int i = 0;
for (DateTime dt = DateTime.Parse("2009-6-1"); dt < DateTime.Parse("2009-7-15"); dt = dt.AddDays(1))
{
string str = dt.DayOfWeek.ToString("D");
// Console.WriteLine(dt.DayOfWeek.ToString());
// Console.WriteLine(str);
if (str != "6" && str != "0")
{
i++;
}
}
Console.WriteLine(i);
Console.Read();
}
}
}
using System;
using System.Collections.Generic;
using System.Text;
using System.Drawing;
using System.Drawing.Imaging;
using System.Windows.Forms;
using System.Reflection;
using System.Collections;
namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{
int i = 0;
for (DateTime dt = DateTime.Parse("2009-6-1"); dt < DateTime.Parse("2009-7-15"); dt = dt.AddDays(1))
{
string str = dt.DayOfWeek.ToString("D");
//Console.WriteLine(str);
if (str == "1" || str == "6")
{
i++;
}
}
Console.WriteLine(i);
Console.Read();
}
}
}
DateTime dt1 = Convert.ToDateTime(TextBox1.Text);
DateTime dt2 = Convert.ToDateTime(TextBox2.Text);
TimeSpan ts = dt2 - dt1;
Label1.Text = ts.Days.ToString();
int n = ts.Days;
switch (dt2.DayOfWeek.ToString())
{
case "Monday":
n = (n) / 7;
break;
case "Tuesday":
n = (n + 1) / 7;
break;
case "Wednesday":
n = (n + 2) / 7;
break;
case "Thursday":
n = (n + 3) / 7;
break;
case "Friday":
n = (n + 4) / 7;
break;
case "Saturday":
n = (n + 5) / 7;
break;
case "Sunday":
n = (n + 6) / 7;
break;
default:
n = 999;
break;
}
Label1.Text += "|"+n.ToString();
using System;
using System.Collections.Generic;
using System.Text;
namespace WeekCount
{
class Program
{
static void Main(string[] args)
{
int i = 0;
for (DateTime dt = DateTime.Parse("2009-6-17"); dt < DateTime.Parse("2009-7-17"); dt=dt.AddDays(1))
{
string str = dt.DayOfWeek.ToString("D");
if (str == "1" || str == "6")
{
i++;
}
}
Console.WriteLine(i);
Console.Read();
}
}
}
public virtual class DateTimeHelper
{
/// <summary>
/// 功能:用于统计从开始到结束时间段内的特定的DayOfWeek的天数
/// </summary>
/// <param name="paramStart">开始时间</param>
/// <param name="paramEnd">结束时间</param>
/// <param name="paramDayOfWeek">要统计的DayOfWeek</param>
/// <returns>返回从开始到结束时间段内的特定的DayOfWeek的天数</returns>
public static Int32 CalcualteDays(DateTime paramStart, DateTime paramEnd, params DayOfWeek[] paramDayOfWeek)
{
TimeSpan tempStartEndTimeSpan = new TimeSpan();
Int32 tempEffectiveDaysCount = 0;
Int32 tempTotalDayCount = 0;
int tempLeftDays = 0;
switch (paramDayOfWeek.Length)
{
case 0:
tempTotalDayCount = 0;
tempEffectiveDaysCount = 0;
break;
default:
if (paramEnd < paramStart)
{
DateTime? tempExchange = null;
tempExchange = paramEnd;
paramEnd = paramStart;
paramStart = (DateTime)tempExchange;
}
tempStartEndTimeSpan = paramEnd - paramStart;
tempTotalDayCount = tempStartEndTimeSpan.Days + 1;
if (tempTotalDayCount >= 7)
{
tempEffectiveDaysCount = (tempTotalDayCount / 7) * paramDayOfWeek.Length;
tempLeftDays = tempTotalDayCount % 7;
if (tempLeftDays > 0)
{
CalcualteDays(tempLeftDays, ref tempEffectiveDaysCount, paramStart, paramDayOfWeek);
}
}
else
{
tempLeftDays = tempTotalDayCount;
CalcualteDays(tempLeftDays, ref tempEffectiveDaysCount, paramStart, paramDayOfWeek);
}
break;
}
return tempEffectiveDaysCount;
}
/// <summary>
/// 用于统计剩余天数少于7天的特定的DayOfWeek的天数
/// </summary>
/// <param name="paramLeftDays">剩余的天数(少于7天)</param>
/// <param name="paramEffectiveDaysCount">用于累加的符合特定DayOfWeek的累加器</param>
/// <param name="paramStart">用于统计的开始日期</param>
/// <param name="paramDayOfWeek">特定的DayOfWeek</param>
private static void CalcualteDays(Int32 paramLeftDays, ref Int32 paramEffectiveDaysCount, DateTime paramStart, DayOfWeek[] paramDayOfWeek)
{
for (int i = 0; i < paramLeftDays; i++)
{
paramStart = paramStart.AddDays(i);
for (int j = 0; j < paramDayOfWeek.Length; j++)
{
if (paramDayOfWeek[j] == paramStart.DayOfWeek)
{
paramEffectiveDaysCount++;
}
}
}
}
}