C# 和SQL 计算时间:周一,周日,月初,月末,季初,季末...
public void GetDate(DateTime thisDate, out DateTime endWeek, out DateTime endMonth, out DateTime endQuarter, out DateTime endYear) { //DateTime dt = Convert.ToDateTime(thisDate.ToString("yyyy-MM-dd")); //DateTime startWeek = dt.AddDays(1 - Convert.ToInt32(dt.DayOfWeek.ToString("d"))); //本 周周一 //endWeek = startWeek.AddDays(7).AddSeconds(-1); //本 周周日 //DateTime beginMonth = dt.AddDays(1 - dt.Day); //本月月初 //endMonth = beginMonth.AddMonths(1).AddSeconds(-1); //本月月末 //DateTime startQuarter = dt.AddMonths(0 - (dt.Month - 1) % 3).AddDays(1 - dt.Day); //本 季度初 //endQuarter = startQuarter.AddMonths(3).AddSeconds(-1); //本 季度末 //DateTime startYear = new DateTime(dt.Year, 1, 1); //下年 年初 //endYear = new DateTime(dt.Year + 1, 1, 1).AddSeconds(-1); //本年 年末 DateTime dt = Convert.ToDateTime(thisDate.ToString("yyyy-MM-dd")); DateTime startWeek; if (Convert.ToInt32(dt.DayOfWeek.ToString("d")) == 0) { startWeek = dt.AddDays(-6); } else { startWeek = dt.AddDays(1 - Convert.ToInt32(dt.DayOfWeek.ToString("d"))); //本 周周一 } // DateTime startWeek = dt.AddDays(1 - Convert.ToInt32(dt.DayOfWeek.ToString("d"))); //本 周周一 endWeek = startWeek.AddDays(7).AddSeconds(-1); //本 周周日 DateTime beginMonth = dt.AddDays(1 - dt.Day); //本月月初 endMonth = beginMonth.AddMonths(1).AddSeconds(-1); //本月月末 DateTime startQuarter = dt.AddMonths(0 - (dt.Month - 1) % 3).AddDays(1 - dt.Day); //本 季度初 endQuarter = startQuarter.AddMonths(3).AddSeconds(-1); //本 季度末 DateTime startYear = new DateTime(dt.Year, 1, 1); //下年 年初 endYear = new DateTime(dt.Year + 1, 1, 1).AddSeconds(-1); //本年 年末 }
1.上面注掉的,如果传入时间是星期天的话,就会把下周一当成本周一.在周的计算上就会有问题.
========================================================================================
2.下面的是在SQL中计算相关的一些时间参数.
DECLARE @MondayTime varchar(10) DECLARE @SundayTime varchar(10) DEClARE @ThisDate datetime DEClARE @EndWeekDate datetime --周末时间 Declare @EndMonth datetime --月末时间 DECLARE @EndQuarter datetime --季末时间 set @ThisDate=dateadd(day,-1,getdate()) IF(datepart(weekday,@ThisDate)-2 < 0) BEGIN SET @MondayTime = convert(varchar(10),dateadd(dd,-1,@ThisDate)-(datepart(weekday,dateadd(dd,-1,@ThisDate))-2),120) SET @SundayTime = convert(varchar(10),dateadd(dd,-1,@ThisDate)+(8-datepart(weekday,dateadd(dd,-1,@ThisDate))),120) END ELSE BEGIN SET @MondayTime =convert(varchar(10),@ThisDate-(datepart(weekday,@ThisDate)-2),120) SET @SundayTime =convert(varchar(10),@ThisDate+(8-datepart(weekday,@ThisDate)),120) END set @EndWeekDate = convert(datetime,@SundayTime) set @EndWeekDate= dateadd(second,59,dateadd(minute,59,dateadd(hour,23,@EndWeekDate))) --select @EndWeekDate set @EndMonth=dateadd(second,59,dateadd(minute,59,dateadd(hour,23,dateadd(month,1+datediff(month,0,@ThisDate),0)-1))) --select @EndMonth as endMonth declare @Num int; declare @spanMonth int; set @Num= datepart(MM,@EndMonth) if(@Num<=3) set @spanMonth = 3-@Num else if(@Num>3 and @Num<=6) set @spanMonth = 6-@Num else if(@Num>6 and @Num<=9) set @spanMonth = 9-@Num else if(@Num>9 and @Num<=12) set @spanMonth = 12-@Num set @EndQuarter= dateadd(month, @spanMonth,@EndMonth) --select @EndQuarter as endQuarter