sqlserver怎么读取时间范围（SQLSERVER创建时间维度表）

在创建时间维度的代码中添加 SET DATEFIRST 1，表示每周以周一开始，我来为大家讲解一下关于sqlserver怎么读取时间范围?跟着小编一起来看一看吧!

sqlserver怎么读取时间范围

在创建时间维度的代码中添加 SET DATEFIRST 1，表示每周以周一开始

---

USE db_name

GO

SET NOCOUNT ON

-- 设置每周的起始天为周一

SET DATEFIRST 1

IF OBJECT_ID('table_name','U') IS NOT NULL

DROP TABLE table_name

GO

CREATE TABLE table_name

(

DateKey INT PRIMARY KEY,

FullDate DATE NOT NULL,

[DateName] NVARCHAR(20),

DayNumberOfWeek TINYINT NOT NULL,

DayNameOfWeek NVARCHAR(10) NOT NULL,

DayNumberOfMonth TINYINT NOT NULL,

DayNumberOfYear SMALLINT NOT NULL,

WeekNumberOfYear TINYINT NOT NULL,

EnglishMonthName NVARCHAR(10) NOT NULL,

MonthNumberOfYear TINYINT NOT NULL,

CalendarQuarter TINYINT NOT NULL,

CalendarSemester TINYINT NOT NULL,

CalendarYear SMALLINT NOT NULL

)

DECLARE @StartDate DATETIME

DECLARE @EndDate DATETIME

SELECT @StartDate = '2021-01-01',

@EndDate = '2022-12-31'

WHILE (@StartDate <= @EndDate)

BEGIN

INSERT INTO table_name

(

DateKey,

FullDate,

[DateName],

DayNumberOfWeek,

DayNameOfWeek,

DayNumberOfMonth,

DayNumberOfYear,

WeekNumberOfYear,

EnglishMonthName,

MonthNumberOfYear,

CalendarQuarter,

CalendarSemester,

CalendarYear

)

SELECT CAST(CONVERT(VARCHAR(8),@StartDate,112) AS INT) AS DateKey,

--参数为112时间格式为YYYYMMDD

CONVERT(VARCHAR(10), @StartDate,20) AS FullDate,

--参数为20时间格式为MM/DD/YY

CONVERT(VARCHAR(20), @StartDate,106) AS [DateName],--参数为106时间格式为MM DD YY

DATEPART(DW,@StartDate) AS DayNumberOfWeek,

DATENAME(DW,@StartDate) AS DayNameOfWeek,

DATENAME(DD,@StartDate) AS [DayOfMonth],

DATENAME(DY,@StartDate) AS [DayOfYear],

DATEPART(WW,@StartDate) AS WeekNumberOfYear,

DATENAME(MM,@StartDate) AS EnglishMonthName,

DATEPART(MM,@StartDate) AS MonthNumberOfYear,

DATEPART(QQ,@StartDate) AS CalendarQuarter,

CASE WHEN DATEPART(MM,@StartDate) BETWEEN 1 AND 6

THEN 1

ELSE 2

END AS CalendarSemester,

DATEPART(YY,@StartDate) AS CalendarYear

SET @StartDate = @StartDate 1

END

GO

,