Sql 根据当前日期获取当天、周、月、季度、年的开始和结束时间

declare @NowDT datetime = getdate()
select   当前时间=getdate(),
		 今天开始时间 = dateadd(ms, 0,dateadd(day,      datediff(day,    0,@NowDT),0)),
		 今天结束时间 = dateadd(ms,-3,dateadd(day,    1+datediff(day,    0,@NowDT),0)),
		 本周开始时间 = dateadd(ms, 0,dateadd(week,     datediff(week,   0,@NowDT),0)),
		 本周结束时间 = dateadd(ms,-3,dateadd(week,   1+datediff(week,   0,@NowDT),0)),
		 本月开始时间 = dateadd(ms, 0,dateadd(MONTH,    datediff(MONTH,  0,@NowDT),0)),
		 本月结束时间 = dateadd(ms,-3,dateadd(MONTH,  1+datediff(MONTH,  0,@NowDT),0)),
		 本季开始时间 = dateadd(ms, 0,dateadd(QUARTER,  datediff(QUARTER,0,@NowDT),0)),
		 本季结束时间 = dateadd(ms,-3,dateadd(QUARTER,1+datediff(QUARTER,0,@NowDT),0)),
		 本年开始时间 = dateadd(ms, 0,dateadd(YEAR,     datediff(YEAR,   0,@NowDT),0)),
		 本年结束时间 = dateadd(ms,-3,dateadd(YEAR,   1+datediff(YEAR,   0,@NowDT),0)),

		 上月开始时间 = dateadd(ms, 0,dateadd(MONTH, -1+datediff(MONTH,  0,@NowDT),0)),
		 上月结束时间 = dateadd(ms,-3,dateadd(MONTH,    datediff(MONTH,  0,@NowDT),0))

循环插入指定年的日历

BEGIN TRAN 

--1、创建临时表存储数据
CREATE TABLE [dbo].#Plan_Week(
	[DateID] [int] IDENTITY(1,1) NOT NULL,
	[Date] [date] NULL,
	[DayOFYear] [int] NULL,
	[WeekName] [varchar](50) NULL,
	[WeekStartDT] [datetime] NULL,
	[WeekEndDT] [datetime] NULL,
	[QuarterNum] [varchar](50) NULL,
	[QuarterStartDT] [datetime] NULL,
	[QuarterEndDT] [datetime] NULL,
	[WeekNum] [int] NULL,
 CONSTRAINT [PK_Plan_Week] PRIMARY KEY CLUSTERED 
(
	[DateID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]  --自增主键


DECLARE @BeginDate DATETIME,	--开始时间
		@EndDate   DATETIME,	--结束时间 
		@Year	   varchar(10),	--插入年 自增数据
		@i		   int =0 ,		--计数器
		@curdate   date			--当前日期

TRUNCATE TABLE #Plan_Week --清空临时表

SET @Year ='2020' --插入指定年份的日历信息
SELECT @BeginDate=CAST(@Year AS VARCHAR)+'-01-01';					 --计算年度起始日期
SELECT @EndDate=CAST(CAST(@Year+1 AS VARCHAR)+'-01-01' AS DATETIME)-1--计算年度结束日期

--当年的总天数
DECLARE @YearCount FLOAT
SELECT @YearCount=DATEPART(DAYOFYEAR,@EndDate)

--循环插入数据 
while @i <@YearCount
	begin
		set @curdate = dateadd(dd,@i,@BeginDate)
		insert into [dbo].#Plan_Week(    Date,		       [DayOFYear],					  WeekName,								  WeekStartDT,									  WeekEndDT,				QuarterNum,									QuarterStartDT,										  QuarterEndDT)
		select			     @curdate,DateName(DAYOFYEAR,@curdate),	DateName(WEEKDAY,@curdate),	dateadd(week,datediff(week,0,@curdate),0),dateadd(week,1+datediff(week,0,@curdate),0)-1,DATENAME(quarter,@curdate),dateadd(quarter,datediff(quarter,0,@curdate),0),dateadd(quarter,1+datediff(quarter,0,@curdate),0)-1			
		set @i = @i + 1
	end 

--更新周、季度结束日期和当前日期是第多少周（WeekNum）
/*
备注：
Datetime 从 1753 年 1 月 1 日到 9999 年 12 月 31 日的日期和时间数据，精确度为百分之三秒（等于 3.33 毫秒或 0.00333 秒）。如下表所示，把值调整到 .000、.003、或 .007 秒的增量。

示例					调整后的示例 
01/01/98 23:59:59.999	1998-01-02 00:00:00.000 

01/01/98 23:59:59.995,
01/01/98 23:59:59.996,  1998-01-01 23:59:59.997
01/01/98 23:59:59.997, 
01/01/98 23:59:59.998  

01/01/98 23:59:59.992, 
01/01/98 23:59:59.993,  1998-01-01 23:59:59.993 
01/01/98 23:59:59.994 

01/01/98 23:59:59.990   1998-01-01 23:59:59.990 
01/01/98 23:59:59.991 

*/
update [dbo].#Plan_Week
set WeekEndDT    = dateadd(ss,-1,DATEADD(DD,1,WeekEndDT)),
	QuarterEndDT = dateadd(ss,-1,DATEADD(DD,1,QuarterEndDT)),
	WeekNum      = DATEPART(week,Date)

SELECT * FROM #Plan_Week

ROLLBACK TRAN