/// <summary>
/// 請假輸入兩個日期：（只考慮最多相差一個月）對於不同月份的數據考慮月份數據分界
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button1_Click(object sender, EventArgs e)
{
DateTime enterStar,enterEnd,stardate1, endate1, stardate2, enddate2;
int betweenyear, betweenmonth, betweenday;
enterStar=dateTimePicker1.Value;
enterEnd=dateTimePicker2.Value;
TimeSpan ts = enterEnd - enterStar;
betweenyear = TimeSpanExtensions.GetYears(ts);
betweenmonth = TimeSpanExtensions.GetMonths(ts);
betweenday = ts.Days;
//betweenday=enterStar.CompareTo(enterEnd);
if (betweenday > 0)
{
if (betweenyear == 0 && betweenmonth == 1)//同年不同月
{
stardate1 = dateTimePicker1.Value;//月初
dateTimePicker3.Value = stardate1;
endate1 = new DateTime(stardate1.Year, stardate1.Month, DateTime.DaysInMonth(stardate1.Year, stardate1.Month));//DaysInMonth 指定年月中的天數
dateTimePicker4.Value = endate1;//月末
stardate2 = new DateTime(enterEnd.Year, enterEnd.Month, 1);
dateTimePicker5.Value = stardate2;//月初
enddate2 = enterEnd;
dateTimePicker6.Value = enddate2;//月末
}
if (betweenyear == 1 && betweenmonth == 1)//不同年不同月
{
stardate1=enterStar;//月初
dateTimePicker3.Value = stardate1;
endate1=new DateTime(stardate1.Year, stardate1.Month, DateTime.DaysInMonth(stardate1.Year, stardate1.Month));//月末
dateTimePicker4.Value = endate1;
stardate2=new DateTime(enterEnd.Year, enterEnd.Month, 1); //月初
dateTimePicker5.Value = stardate2;
enddate2=enterEnd;//月末
dateTimePicker6.Value = enddate2;
}
if (betweenyear == 0 && betweenmonth == 0)//同年同月
{
stardate1 = enterStar;//月初
dateTimePicker3.Value = stardate1;
endate1 = enterEnd;//月末
dateTimePicker4.Value = endate1;
}
}
}
---sql 請假輸入兩個日期：（只考慮最多相差一個月）對於不同月份的數據考慮月份數據分界
---1.要判斷是否同一年同一月，不同年，不同月要分開顯示兩組不同月日期之間數據
---2.同年不同月,要分開顯示兩組不同月日期之間數據
---3.同年同月不用分組顯示
declare @enterStar datetime,@enterEnd datetime,@stardate1 datetime,@endate1 datetime,@stardate2 datetime,@enddate2 datetime
declare @betweenyear int,@betweenmonth int,@betweenday int
set @enterStar='2012-12-05'
set @enterEnd='2013-01-25'
select @betweenyear=DATEDIFF(year, @enterStar, @enterEnd)
SELECT @betweenmonth =DATEDIFF(month, @enterStar, @enterEnd)
SELECT @betweenday=DATEDIFF(day, @enterStar, @enterEnd)
if (@betweenday>0)
begin
if(@betweenyear=0 and @betweenmonth=1)
begin
select @stardate1=@enterStar--Dateadd(mm,datediff(mm,0,@enterStar),0) --月初
select @endate1=Dateadd(ms,-3,dateadd(mm,datediff(m,0,@enterStar)+1,0)) --月末
select @stardate2=Dateadd(mm,datediff(mm,0,@enterEnd),0) --月初
select @enddate2=@enterEnd--Dateadd(ms,-3,dateadd(mm,datediff(m,0,@enterEnd)+1,0)) --月末
select @stardate1,@endate1,@stardate2,@enddate2
end
if(@betweenyear=1 and @betweenmonth=1)
begin
select @stardate1=@enterStar--Dateadd(mm,datediff(mm,0,@enterStar),0) --月初
select @endate1=Dateadd(ms,-3,dateadd(mm,datediff(m,0,@enterStar)+1,0)) --月末
select @stardate2=Dateadd(mm,datediff(mm,0,@enterEnd),0) --月初
select @enddate2=@enterEnd--Dateadd(ms,-3,dateadd(mm,datediff(m,0,@enterEnd)+1,0)) --月末
select @stardate1,@endate1,@stardate2,@enddate2
end
if(@betweenyear=0 and @betweenmonth=0)
begin
select @stardate1=@enterStar
select @endate1=@enterEnd
select @stardate1,@endate1
end
end
