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

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16436858/viewspace-673726/，如需轉載，請註明出處，否則將追究法律責任。