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 ='' --插入指定年份的日历信息SELECT @BeginDate=CAST(@Year AS VARCHAR)+'-01-01'; --计算年度起始日期SELECT @EndDate=CAST(CAST(@Year+1 AS VARCHAR)+'-01-01' AS DATETIME)-1--计算年度结束日期--当年的总天数DECLARE @YearCount FLOATSELECT @YearCount=DATEPART(DAYOFYEAR,@EndDate)--循环插入数据 while @i <@YearCountbeginset @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)-1set @i = @i + 1end --更新周、季度结束日期和当前日期是第多少周（WeekNum）/*备注：Datetime 从 1753 年 1 月 1 日到 9999 年 12 月 31 日的日期和时间数据，精确度为百分之三秒（等于 3.33 毫秒或 0.00333 秒）。如下表所示，把值调整到 .000、.003、或 .007 秒的增量。示例调整后的示例 01/01/98 23:59:59.9991998-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.99701/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_Weekset WeekEndDT = dateadd(ss,-1,DATEADD(DD,1,WeekEndDT)),QuarterEndDT = dateadd(ss,-1,DATEADD(DD,1,QuarterEndDT)),WeekNum= DATEPART(week,Date)SELECT * FROM #Plan_WeekROLLBACK TRAN
