C# 和SQL 计算时间:周一,周日,月初,月末,季初,季末...
public void GetDate(DateTime thisDate, out DateTime endWeek, out DateTime endMonth, out DateTime endQuarter, out DateTime endYear) |
02 |
{ |
03 |
//DateTime dt = Convert.ToDateTime(thisDate.ToString("yyyy-MM-dd")); |
04 |
//DateTime startWeek = dt.AddDays(1 - Convert.ToInt32(dt.DayOfWeek.ToString("d"))); //本 周周一 |
05 |
//endWeek = startWeek.AddDays(7).AddSeconds(-1); //本 周周日 |
06 |
//DateTime beginMonth = dt.AddDays(1 - dt.Day); //本月月初 |
07 |
//endMonth = beginMonth.AddMonths(1).AddSeconds(-1); //本月月末 |
08 |
//DateTime startQuarter = dt.AddMonths(0 - (dt.Month - 1) % 3).AddDays(1 - dt.Day); //本 季度初 |
09 |
//endQuarter = startQuarter.AddMonths(3).AddSeconds(-1); //本 季度末 |
10 |
//DateTime startYear = new DateTime(dt.Year, 1, 1); //下年 年初 |
11 |
//endYear = new DateTime(dt.Year + 1, 1, 1).AddSeconds(-1); //本年 年末 |
12 |
13 |
DateTime dt = Convert.ToDateTime(thisDate.ToString("yyyy-MM-dd")); |
14 |
DateTime startWeek; |
15 |
if (Convert.ToInt32(dt.DayOfWeek.ToString("d")) == 0) |
16 |
{ |
17 |
startWeek = dt.AddDays(-6); |
18 |
} |
19 |
else |
20 |
{ |
21 |
startWeek = dt.AddDays(1 - Convert.ToInt32(dt.DayOfWeek.ToString("d"))); //本 周周一 |
22 |
} |
23 |
// DateTime startWeek = dt.AddDays(1 - Convert.ToInt32(dt.DayOfWeek.ToString("d"))); //本 周周一 |
24 |
endWeek = startWeek.AddDays(7).AddSeconds(-1); //本 周周日 |
25 |
DateTime beginMonth = dt.AddDays(1 - dt.Day); //本月月初 |
26 |
endMonth = beginMonth.AddMonths(1).AddSeconds(-1); //本月月末 |
27 |
DateTime startQuarter = dt.AddMonths(0 - (dt.Month - 1) % 3).AddDays(1 - dt.Day); //本 季度初 |
28 |
endQuarter = startQuarter.AddMonths(3).AddSeconds(-1); //本 季度末 |
29 |
DateTime startYear = new DateTime(dt.Year, 1, 1); //下年 年初 |
30 |
endYear = new DateTime(dt.Year + 1, 1, 1).AddSeconds(-1); //本年 年末 |
31 |
} |
1.上面注掉的,如果传入时间是星期天的话,就会把下周一当成本周一.在周的计算上就会有问题.
========================================================================================
2.下面的是在SQL中计算相关的一些时间参数.
01 |
DECLARE @MondayTime varchar(10) |
02 |
DECLARE @SundayTime varchar(10) |
03 |
DEClARE @ThisDate datetime |
04 |
DEClARE @EndWeekDate datetime --周末时间 |
05 |
Declare @EndMonth datetime --月末时间 |
06 |
DECLARE @EndQuarter datetime --季末时间 |
07 |
set @ThisDate=dateadd(day,-1,getdate()) |
08 |
IF(datepart(weekday,@ThisDate)-2 < 0) |
09 |
BEGIN |
10 |
SET @MondayTime = convert(varchar(10),dateadd(dd,-1,@ThisDate)-(datepart(weekday,dateadd(dd,-1,@ThisDate))-2),120) |
11 |
SET @SundayTime = convert(varchar(10),dateadd(dd,-1,@ThisDate)+(8-datepart(weekday,dateadd(dd,-1,@ThisDate))),120) |
12 |
END |
13 |
ELSE |
14 |
BEGIN |
15 |
SET @MondayTime =convert(varchar(10),@ThisDate-(datepart(weekday,@ThisDate)-2),120) |
16 |
SET @SundayTime =convert(varchar(10),@ThisDate+(8-datepart(weekday,@ThisDate)),120) |
17 |
END |
18 |
|
19 |
set @EndWeekDate = convert(datetime,@SundayTime) |
20 |
set @EndWeekDate= dateadd(second,59,dateadd(minute,59,dateadd(hour,23,@EndWeekDate))) |
21 |
--select @EndWeekDate |
22 |
|
23 |
|
24 |
set @EndMonth=dateadd(second,59,dateadd(minute,59,dateadd(hour,23,dateadd(month,1+datediff(month,0,@ThisDate),0)-1))) |
25 |
--select @EndMonth as endMonth |
26 |
|
27 |
declare @Num int; |
28 |
declare @spanMonth int; |
29 |
set @Num= datepart(MM,@EndMonth) |
30 |
if(@Num<=3) |
31 |
set @spanMonth = 3-@Num |
32 |
else if(@Num>3 and @Num<=6) |
33 |
set @spanMonth = 6-@Num |
34 |
else if(@Num>6 and @Num<=9) |
35 |
set @spanMonth = 9-@Num |
36 |
else if(@Num>9 and @Num<=12) |
37 |
set @spanMonth = 12-@Num |
38 |
set @EndQuarter= dateadd(month, @spanMonth,@EndMonth) |
39 |
|
40 |
--select @EndQuarter as endQuarter |
浙公网安备 33010602011771号