介绍 (Introduction)

There is a common need in reporting to aggregate or return data that is crunched based on date attributes. These may include weekdays, holidays, quarters, or time of year. While any of this information can be calculated on the fly, a calendar table can save time, improve performance, and increase the consistency of data returned by our important reporting processes.

在报告中普遍需要聚合或返回基于日期属性处理的数据。 这些可能包括工作日，节假日，季度或一年中的时间。 尽管可以即时计算任何此类信息，但日历表可以节省时间，提高性能并提高我们重要报告流程返回的数据的一致性。

什么是日历表，为什么有用？ (What is a Calendar Table and Why is it Useful?)

A calendar table is a permanent table containing a list of dates and various components of those dates. These may be the result of DATEPART operations, time of year, holiday analysis, or any other creative operations we can think of.

日历表是一个永久性表，其中包含日期列表以及这些日期的各个组成部分。 这些可能是DATEPART操作，一年中的时间，假期分析或我们可以想到的任何其他创意操作的结果。

The primary key of this table will always be the date, or some easy-to-use representation of that date. Each subsequent column will be an attribute of that date, where the types and size of those columns can vary greatly. At first glance, it may seem that a table such as this would be superfluous, and that this data is easy to generate, but oftentimes as our reporting needs become complex, so does the creation, maintenance, and usage of this data.

该表的主键将始终是日期或该日期的某些易于使用的表示形式。 随后的每一列都是该日期的属性，这些列的类型和大小可以有很大的不同。 乍一看，这样的表似乎是多余的，并且易于生成此数据，但是通常由于我们的报告需求变得复杂，因此创建，维护和使用此数据也是如此。

As a result, storing calendar data in a permanent location can be an easy solution. Here are some reasons why this data is useful and why storing it in a dedicated table can be a great decision:

因此，将日历数据存储在永久位置可能是一个简单的解决方案。 以下是一些有用的理由，以及为什么将这些数据存储在专用表中是一个很好的决定的原因：

• Data is relatively easy to generate and requires little maintenance once created.
  数据相对容易生成，创建后几乎不需要维护。
• Calendar data can be used to service any reports that require it, removing the need to recreate it in each report.
  日历数据可用于为需要它的任何报告提供服务，而无需在每个报告中重新创建它。
• We can implement a large number of calendar metrics, and can easily add more as needed.
  我们可以实现大量的日历指标，并且可以根据需要轻松添加更多指标。
• Calendar data is tiny in terms of space used. Performance against this data is generally quite fast.
  日历数据占用的空间很小。 通常，针对此数据的性能非常快。
• Complex reports can be simplified by removing commonly used DATEPART and DATEADD computations.
  通过删除常用的DATEPART和DATEADD计算，可以简化复杂的报告。
• Important business logic, such as holidays, can be centralized and maintained in a single location.
  重要的业务逻辑（例如假期）可以集中并维护在一个位置。
• Maintaining calendar data in a single table ensures we do not encounter inconsistencies between different reports, reporting systems, or applications that need it.
  在单个表中维护日历数据可确保我们不会在不同的报表，报表系统或需要它的应用程序之间遇到不一致的情况。

There are many different reasons why a calendar table can be useful—this article is our opportunity to create one from scratch, populate it with data, analyze it, and put it to good use!

日历表之所以有用，有很多不同的原因-本文是我们从零开始创建一个日历表，使用数据填充，对其进行分析并加以利用的机会！

实施日历表 (Implementing a Calendar Table)

Our first step is to identify and define metrics that we want to collect. This is where we should look at our reporting needs and determine what sorts of date-related calculations we perform on a regular basis. For our examples here, we will include 33 different metrics (plus the calendar date itself), though you are free to add more as needed. Once introduced, we’ll walk through how to populate this data, and then how to use it.

我们的第一步是识别并定义我们要收集的指标。 在这里，我们应该查看报告需求，并确定我们定期执行哪种与日期相关的计算。 对于此处的示例，我们将包括33种不同的指标（加上日历日期本身），不过您可以根据需要随意添加更多指标。 引入后，我们将逐步介绍如何填充此数据，以及如何使用它。

日期零件 (Date Parts)

The simplest metrics are basic date components, including:

最简单的指标是基本日期成分，包括：

• Calendar Month: The numeric representation of the month, a number from 1-12.
• 日历月份 ： 月份的数字表示形式，从1到12的数字。
• Calendar Day: The numeric representation of the calendar day, a number from 1-31. The maximum value depends on the month and on whether it is a leap year.
• 日历日 ： 日历日的数字表示形式，是1到31之间的数字。 最大值取决于月份以及是否为leap年。
• Calendar Year: The numeric representation of the year, such as 1979 or 2017.
• 日历年份 ： 年份的数字表示形式，例如1979或2017。
• Calendar Quarter: The numeric representation of the quarter, a number from 1-4.
• 日历季度 ： 季度的数字表示形式，为1-4的数字。
• Day Name: The common name for the day of the week, such as Tuesday or Saturday. 日期名称 ：星期几的通用名称，例如星期二或星期六。
• Day of Week: The numeric representation of the day of the week, a number from 1(Sunday) through 7 (Saturday). In some countries the number 1 is used to represent Monday, though here we will use Sunday for this calculation. 星期几 ：一周中每天的数字表示，一些从1（星期日）至7日（星期六）。 在某些国家/地区，数字1用于表示星期一，尽管此处我们将使用星期日进行计算。
• Month Name: The common name for the month, such as February or October.
• 月份名称 ： 月份的通用名称，例如2月或10月。

These are all bits and pieces of the date itself and are useful whenever we are looking to find out metrics on specific days of the week, fiscal quarters, or other date parts.

这些都是日期本身的所有细节，每当我们要查找一周中特定日期，财政季度或其他日期部分的指标时，这些功能就很有用。

相对时间点 (Relative Points in Time)

Knowing when a date is, with respect to other calendar metrics, can be very handy in understanding how business changes over time. The following metrics allow you to determine in what part of the week, month, or year a date occurs:

相对于其他日历指标而言，了解日期的时间对于理解业务随时间的变化非常方便。 以下度量标准使您可以确定日期在一周，一月或一年中的哪个部分发生：

• Day of Week in Month: The occurrence of a day of week within the current month. Ie: The third Thursday of the current month. 每月的星期几：在当前月份中星期几的发生。 即：本月的第三个星期四。
• Day of Week in Year: The occurrence of a day of week within the current year. Ie: The seventeenth Monday of the current year. 一年中的星期几：当年中星期几的发生。 即：本年的第十七个星期一。
• Day of Week in Quarter: The occurrence of a day of week within the current quarter. Ie: The seventh Saturday of the current quarter. 季度中的星期几：当前季度中星期几的发生。 即：当前季度的第七个星期六。
• Day of Quarter: The day number within the current quarter. 季度的天：当前季度内的天数。
• Day of Year: The day number out of the current year. 每年的日子：当年的天数。
• Week of Month: The week number within the current month. With this calculation, the weeks count starting on the first of the month, regardless of the day of week.
• 每月的星期： 当月的星期数。 通过此计算，周数从每月的第一天开始计算，而与周几无关。
• Week of Quarter: The week number within the current quarter. 季度周 ：当前季度内的周数。
• Week of Year: The week number within the current year. 每年的第几周：当年的第几周。
• First Date of Week: The start date of the week. Sunday is assumed here, but could be defined differently. 星期的第一个日期：星期的开始日期。 这里假定为星期日，但可以定义不同。
• Last Date of Week: The end date of the week. Saturday is assumed here, but could be defined differently. 一周的最后日期：一周的结束日期。 这里假定为星期六，但可以定义为不同的地方。
• First Date of Month: The first date of the current month.
• 本月的第一个日期： 当月的第一个日期。
• Last Date of Month: The last date of the current month.
• 月的最后日期： 当月的最后日期。
• First Date of Quarter: The first date of the current quarter. 季度的第一个日期：当前季度的第一个日期。
• Last Date of Quarter: The last date of the current quarter. 季度的最后日期：当前季度的最后日期。
• First Date of Year: The first date of the current year. 每年的第一个日期：当年的第一个日期。
• Last Date of Year: The last date of the current year. 年份的最后日期：当年的最后日期。

These metrics allow us to easily determine when holidays or special dates occur. In addition, they can be used to assist with special processes that occur during specific weeks, such as tax preparation, invoicing, or other routing operations.

这些度量标准使我们可以轻松确定何时放假或特殊日期。 此外，它们可用于协助在特定星期内发生的特殊流程，例如报税，开票或其他Craft.io路线操作。

The “Day of Week of…” metrics are measures of how many of a given day have occurred thus far in a time period. The following illustration shows this metric for Wednesdays in March, 2017:

“一周中的一天…”度量标准是到目前为止在一个时间段内发生了给定一天的次数的度量。 下图显示了2017年3月星期三的此指标：

The “Day of Week of Month” for the 1^st is 1, the 8^th is 2, the 15^th is 3, and so on. The similar metrics for quarter and year are calculated in the same fashion, by asking, “So far this year, how many Mondays have we had”, assuming the date we are looking at is a Monday.

对于第1 ^次的“本月的星期几”为1时， ^第 8是图2中， ^第 15 ^次是3，等等。 假设我们要查看的日期是星期一，则通过询问“今年到目前为止，我们有多少个星期一”，以相同的方式计算季度和年份的类似指标。

Knowing boundaries, such as the start and end of weeks can allow for year-over-year trending by week, or for quick data crunching by specific periods in time.

了解边界（例如几周的开始和结束）可以允许按周显示逐年趋势，或者可以按特定时间段对数据进行快速处理。

假期和工作日 (Holidays and Business Days)

Many business care about holidays, holiday seasons, and when business days occur. This can influence load on software systems, allocation of resources, employee coverage, and financial trending. Including some data that describes when holidays occur and what they are can greatly assist in this sort of analysis, removing the need for complex, ad-hoc reporting at a later time.

许多企业关心假期，假期和工作日。 这会影响软件系统上的负载，资源分配，员工覆盖范围以及财务趋势。 包括一些描述假期何时发生以及假期是什么的数据，可以极大地帮助进行此类分析，从而在以后不再需要复杂的临时报告。

• Is Holiday? A bit that indicates if a given date is a holiday or not. 是假期吗？ 指示给定日期是否为假期的位。
• Is Holiday Season? A bit that indicates if a given date is part of a holiday season or not. 是假期吗？ 指示给定日期是否属于假日季节的位。
• Holiday Name: Indicates the name of the holiday, if applicable.
• 假日名称 ：指示假日的名称 （如果适用）。
• Holiday Season Name: Indicates the name of the holiday season, if applicable. 假期名称 ：指示假期的名称（如果适用）。
• Is Weekday? A bit that indicates if a given day is a weekday, typically Monday-Friday. 是工作日吗？ 指示给定的一天是否是工作日的位，通常是星期一至星期五。
• Is Business Day? A bit that combines weekday and holiday data to determine if it is a business/work day. 是工作日吗？ 结合工作日和假日数据来确定是否是工作日/工作日的位。
• Previous Business Day: This is the immediately preceding business day前一个工作日 ：这是前一个工作日. 。
• Next Business Day: This is the immediately following business day下一个工作日 ：这是下一个工作日. 。

Business days can vary greatly across different industries. A week off for some may equate to the busiest week of the year for others. Summer vacation for teachers could be prime time for an outdoor amusement park. Being able to enumerate all of these rules into a handful of simple bits can make metrics-gathering significantly faster and easier!

不同行业的工作日差异很大。 对于某些人来说，放假一周可能等同于一年中最繁忙的一周。 老师的暑假可能是户外游乐园的黄金时间。 能够将所有这些规则枚举为少数几个简单的位，可以使收集度量标准的过程变得更快，更简单！

杂项指标 (Miscellaneous Metrics)

We could come up with endless lists of date-related metrics, but for those that didn’t fit into other categories, here are a few examples that could be handy:

我们可以拿出无数个与日期相关的指标列表，但是对于那些不适合其他类别的指标，下面是一些方便的示例：

• Is Leap Year: Is the current date is contained within a leap year, then this bit would be set to 1. 是Le年 ：当前日期是否包含在a年内，那么该位将设置为1。
• Days in Month: Contains the number of days in the current month.
• 每月的天数：包含当月的天数。
• Calendar Date String: A representation of the date in a string, delimited by forward slashes. This is useful for quickly displaying a more familiar string-based date format to the user. Other formats can be stored, too.
• 日历日期字符串 ： 字符串中日期的表示形式，以正斜杠分隔。 这对于快速向用户显示更熟悉的基于字符串的日期格式很有用。 也可以存储其他格式。

The purpose of a calendar table is to improve the speed, accuracy, and ease of reporting. Many of these metrics could easily be calculated on the fly as a report is run, but over time, the need for more complex metrics would make this incredibly messy. Even getting the days in a month could be a nuisance as a CASE statement including details for leap years would be cumbersome.

日历表的目的是提高报告的速度，准确性和便利性。 在运行报表时，可以轻松地即时计算出许多这些指标，但是随着时间的流逝，对更复杂指标的需求将使这一工作变得非常混乱。 即使获得一个月的日子也可能很麻烦，因为CASE声明（包括leap年的详细信息）会很麻烦。

创建Dim_Date表 (Create the Dim_Date Table)

Our first step is to create a calendar table for use in all of our subsequent examples:

我们的第一步是创建一个日历表，以用于所有后续示例中：

 CREATE TABLE dbo.Dim_Date(	Calendar_Date DATE NOT NULL CONSTRAINT PK_Dim_Date PRIMARY KEY CLUSTERED, -- The date addressed in this row.	Calendar_Date_String VARCHAR(10) NOT NULL, -- The VARCHAR formatted date, such as 07/03/2017	Calendar_Month TINYINT NOT NULL, -- Number from 1-12	Calendar_Day TINYINT NOT NULL, -- Number from 1 through 31	Calendar_Year SMALLINT NOT NULL, -- Current year, eg: 2017, 2025, 1984.	Calendar_Quarter TINYINT NOT NULL, -- 1-4, indicates quarter within the current year.	Day_Name VARCHAR(9) NOT NULL, -- Name of the day of the week, Sunday...Saturday	Day_of_Week TINYINT NOT NULL, -- Number from 1-7 (1 = Sunday)	Day_of_Week_in_Month TINYINT NOT NULL, -- Number from 1-5, indicates for example that it's the Nth saturday of the month.	Day_of_Week_in_Year TINYINT NOT NULL, -- Number from 1-53, indicates for example that it's the Nth saturday of the year.	Day_of_Week_in_Quarter TINYINT NOT NULL, -- Number from 1-13, indicates for example that it's the Nth saturday of the quarter.	Day_of_Quarter TINYINT NOT NULL, -- Number from 1-92, indicates the day # in the quarter.	Day_of_Year SMALLINT NOT NULL, -- Number from 1-366	Week_of_Month TINYINT NOT NULL, -- Number from 1-6, indicates the number of week within the current month.	Week_of_Quarter TINYINT NOT NULL, -- Number from 1-14, indicates the number of week within the current quarter.	Week_of_Year TINYINT NOT NULL, -- Number from 1-53, indicates the number of week within the current year.	Month_Name VARCHAR(9) NOT NULL, -- January-December	First_Date_of_Week DATE NOT NULL, -- Date of the first day of this week.	Last_Date_of_Week DATE NOT NULL, -- Date of the last day of this week.	First_Date_of_Month DATE NOT NULL, -- Date of the first day of this month.	Last_Date_of_Month DATE NOT NULL, -- Date of the last day of this month.	First_Date_of_Quarter DATE NOT NULL, -- Date of the first day of this quarter.	Last_Date_of_Quarter DATE NOT NULL, -- Date of the last day of this quarter.	First_Date_of_Year DATE NOT NULL, -- Date of the first day of this year.	Last_Date_of_Year DATE NOT NULL, -- Date of the last day of this year.	Is_Holiday BIT NOT NULL, -- 1 if a holiday	Is_Holiday_Season BIT NOT NULL, -- 1 if part of a holiday season	Holiday_Name VARCHAR(50) NULL, -- Name of holiday, if Is_Holiday = 1	Holiday_Season_Name VARCHAR(50) NULL, -- Name of holiday season, if Is_Holiday_Season = 1	Is_Weekday BIT NOT NULL, -- 1 if Monday-->Friday, 0 for Saturday/Sunday	Is_Business_Day BIT NOT NULL, -- 1 if a workday, otherwise 0.	Previous_Business_Day DATE NULL, -- Previous date that is a work day	Next_Business_Day DATE NULL, -- Next date that is a work day	Is_Leap_Year BIT NOT NULL, -- 1 if current year is a leap year.	Days_in_Month TINYINT NOT NULL -- Number of days in the current month.); 

There are two ways to approach the structure of this data. One is to create computed columns that automatically populate based on the date, and the other is to populate all via script instead. I have chosen to allow for manual population with the rationale being:

有两种方法可以处理此数据的结构。 一种是创建根据日期自动填充的计算列，另一种是通过脚本填充所有列。 我选择允许手动填充，其理由是：

1. Even if we toss 100 years of data into the date table, performance will be good enough that it will populate in approximately a few minutes.
   即使我们将100年的数据丢到日期表中，性能也将足够好，它将在大约几分钟内出现。
2. Being able to use metrics immediately for further calculations Is immensely useful, simplifying code & maintenance. This is especially handy for more complex calculations involving calendars and holidays.
   能够立即将指标用于进一步的计算非常有用，可简化代码和维护。 对于涉及日历和假期的更复杂的计算，这尤其方便。
3. Populating a calendar table is a one-time affair. Once complete, the data is available indefinitely.
   填充日历表是一件一次性的事情。 完成后，数据将无限期可用。

Also note that the primary key on the table is a DATE. Integers have often been used as the primary key on calendar tables in older versions of SQL Server due to the lack of an appropriate DATE data type. Not only is DATE more intuitive and easier to use, but it only consumes 3 bytes instead of 4 for an INT. In theory, a SMALLINT (2 bytes) could be used, but there is some risk of overflow if we decide to trend far into the past or into the future. If for any reason (backward compatibility, etc…) you needed to use an integer, augmenting this process to do so would not be difficult.

另请注意，表上的主键是DATE。 由于缺少适当的DATE数据类型，在SQL Server的较早版本中，整数通常被用作日历表的主键。 DATE不仅更加直观和易于使用，而且仅占用3个字节，而不是一个INT的4个字节。 从理论上讲，可以使用SMALLINT（2字节），但是如果我们决定趋向于过去或将来，则存在溢出的风险。 如果出于任何原因（向后兼容等），您需要使用整数，则扩大此过程的使用并不困难。

Once created, we can move forward with creating a stored procedure that can be used to insert data into this table.

一旦创建，我们就可以继续创建存储过程，该存储过程可用于将数据插入该表中。

填充日历表 (Populating a Calendar Table)

To keep our stored proc simple, we’ll have only 2 parameters: A start and end date. Any existing data in this date range will be deleted and repopulated in its entirety. This allows for easy recreation of data in the event that any changes are made to this process, such as defining holidays or business days.

为了简化存储过程，我们只有两个参数：开始日期和结束日期。 此日期范围内的所有现有数据都将被删除并全部重新填充。 如果对此过程进行了任何更改（例如定义假期或工作日），则可以轻松地重新创建数据。

 CREATE PROCEDURE dbo.Populate_Dim_Date	@Start_Date DATE,	@End_Date DATEASBEGIN	SET NOCOUNT ON; 

With the proc declaration out of the way, we can quickly check for a few anomalous conditions that we would want to alert on:

有了proc声明，我们可以快速检查一些我们想提醒的异常情况：

 IF @Start_Date IS NULL OR @End_Date IS NULLBEGIN	SELECT 'Start and end dates MUST be provided in order for this stored procedure to work.';	RETURN;END 	IF @Start_Date > @End_Date	BEGIN		SELECT 'Start date must be less than or equal to the end date.';		RETURN;	END 

These validations ensure that we are not passing in NULL data or end dates that occur prior to start dates. The error is a nudge on the shoulder instead of throwing a RAISERROR, as this is more of a maintenance utility than an ongoing process.

这些验证可确保我们不会传递NULL数据或开始日期之前的结束日期。 该错误是微不足道的，而不是引发RAISERROR，因为它更多的是维护实用程序，而不是正在进行的过程。

The next step is to remove any data from Dim_Date that falls within the range specified:

下一步是从Dim_Date中删除所有在指定范围内的数据：

 DELETE FROM dbo.Dim_DateWHERE Dim_Date.Calendar_Date BETWEEN @Start_Date AND @End_Date;  

In order to accurately generate our data, I chose a methodical approach in which we declare variables, assign them one at a time, and then insert a row into Dim_Date when complete. The iterative approach is typically going to operate slower than a set-based approach, but the ability to use scalar variables to do so helps keep that runtime acceptable. A set-based approach could be devised with a numbers table or set of CTEs in order to reduce runtime, but the trade-off would be complexity and contention.

为了准确地生成数据，我选择了一种有条理的方法，在该方法中声明变量，一次分配一个变量，然后在完成时在Dim_Date中插入一行。 迭代方法通常比基于集合的方法要慢一些，但是使用标量变量的能力有助于使运行时可接受。 为了减少运行时间，可以设计一个带有数字表或一组CTE的基于集合的方法，但是要权衡的是复杂性和竞争性。

The long laundry list of local variables is as follows:

长长的局部变量清单如下：

 DECLARE @Date_Counter DATE = @Start_Date;DECLARE @Calendar_Date_String VARCHAR(10);DECLARE @Calendar_Month TINYINT;DECLARE @Calendar_Day TINYINT;DECLARE @Calendar_Year SMALLINT;DECLARE @Calendar_Quarter TINYINT;DECLARE @Day_Name VARCHAR(9);DECLARE @Day_of_Week TINYINT;DECLARE @Day_of_Week_in_Month TINYINT;DECLARE @Day_of_Week_in_Year TINYINT;DECLARE @Day_of_Week_in_Quarter TINYINT;DECLARE @Day_of_Quarter TINYINT;DECLARE @Day_of_Year SMALLINT;DECLARE @Week_of_Month TINYINT;DECLARE @Week_of_Quarter TINYINT;DECLARE @Week_of_Year TINYINT;DECLARE @Month_Name VARCHAR(9);DECLARE @First_Date_of_Week DATE;DECLARE @Last_Date_of_Week DATE;DECLARE @First_Date_of_Month DATE;DECLARE @Last_Date_of_Month DATE;DECLARE @First_Date_of_Quarter DATE;DECLARE @Last_Date_of_Quarter DATE;DECLARE @First_Date_of_Year DATE;DECLARE @Last_Date_of_Year DATE;DECLARE @Is_Holiday BIT;DECLARE @Is_Holiday_Season BIT;DECLARE @Holiday_Name VARCHAR(50);DECLARE @Holiday_Season_Name VARCHAR(50);DECLARE @Is_Weekday BIT;DECLARE @Is_Business_Day BIT;DECLARE @Is_Leap_Year BIT;DECLARE @Days_in_Month TINYINT; 

There is a variable for nearly every column, allowing calculations to be kept simple. We also benefit from being able to use any calculations in subsequent operations. @Date_Counter will iterate from @Start_Date through @End_date, as we insert rows into our calendar table.

几乎每一列都有一个变量，使计算保持简单。 我们还受益于能够在后续操作中使用任何计算。 @Date_Counter将从@start_date通过@End_date迭代，因为我们插入行到我们的日历表。

From this point on, we enter a loop, assign values to the variables, and then insert a row into Dim_Date. The following are all of these calculations, along with a description of what they mean:

从这一点开始，我们进入一个循环，为变量赋值，然后在Dim_Date中插入一行。 以下是所有这些计算以及它们的含义的描述：

 SELECT @Calendar_Month = DATEPART(MONTH, @Date_Counter);SELECT @Calendar_Day = DATEPART(DAY, @Date_Counter);SELECT @Calendar_Year = DATEPART(YEAR, @Date_Counter);SELECT @Calendar_Quarter = DATEPART(QUARTER, @Date_Counter);SELECT @Day_of_Week = DATEPART(WEEKDAY, @Date_Counter);SELECT @Day_of_Year = DATEPART(DAYOFYEAR, @Date_Counter);SELECT @Week_of_Year = DATEPART(WEEK, @Date_Counter); 

These seven variables all represent date parts and can be captured using DATEPART and some simple TSQL. Once we have these values, we can derive additional information about the date:

这七个变量都代表日期部分，可以使用DATEPART和一些简单的TSQL捕获。 一旦有了这些值，就可以导出有关日期的其他信息：

 SELECT @Calendar_Date_String = CAST(@Calendar_Month AS VARCHAR(10)) + '/' + CAST(@Calendar_Day AS VARCHAR(10)) + '/' + CAST(@Calendar_Year AS VARCHAR(10)); 

Capturing a string version of the date is handy for quick & reliable display in a specific format. The format MM/DD/YYYY is used above, but could very easily be tinkered with in order to display using other orderings or delimiters. CONVERT may be also used to format the date in a variety of forms. For example, consider the following TSQL:

捕获日期的字符串版本便于以特定格式快速可靠地显示。 上面使用的格式为MM / DD / YYYY，但是可以很容易地修改格式以便使用其他顺序或定界符进行显示。 CONVERT还可以用于以多种形式格式化日期。 例如，考虑以下TSQL：

 SELECT CONVERT(VARCHAR(25), CURRENT_TIMESTAMP, 101);SELECT CONVERT(VARCHAR(25), CURRENT_TIMESTAMP, 102);SELECT CONVERT(VARCHAR(25), CURRENT_TIMESTAMP, 103);SELECT CONVERT(VARCHAR(25), CURRENT_TIMESTAMP, 10);SELECT CONVERT(VARCHAR(25), CURRENT_TIMESTAMP, 107); 

The results show a bunch of ways that we can take a date and reformat it based on local, international, or stylistic formats:

结果显示了很多方法，我们可以根据本地，国际或样式格式确定日期并重新格式化：

 SELECT @Is_Weekday = CASE				WHEN @Day_of_Week IN (1, 7)					THEN 0				ELSE 1			END;SELECT @Is_Business_Day =	@Is_Weekday; 

Weekdays are defined here as all days of the week except Saturday & Sunday (the weekend). In some countries or businesses, this may be defined differently, and can easily be tweaked for those purposes. Business days will be defined as all weekdays that are also not holidays. We will handle holidays near the end of our stored procedure, but will set this equal to @Is_Weekday for now, as a convenience for our future calculations where we will set this equal to zero for any holidays identified.

工作日在这里定义为一周中除周六和周日（周末）以外的所有日子。 在某些国家或地区，此定义可能有所不同，并且可以轻松地针对这些目的进行调整。 工作日将被定义为并非工作日的所有工作日。 我们将在存储过程快要结束时处理假期，但是现在将其设置为等于@Is_Weekday ，以方便我们将来的计算，在此计算中，对于确定的任何假期，都将其设置为零。

 SELECT @Day_Name = CASE @Day_of_Week					WHEN 1 THEN 'Sunday'					WHEN 2 THEN 'Monday'					WHEN 3 THEN 'Tuesday'					WHEN 4 THEN 'Wednesday'					WHEN 5 THEN 'Thursday'					WHEN 6 THEN 'Friday'					WHEN 7 THEN 'Saturday'				END; SELECT @Month_Name = CASE @Calendar_Month					WHEN 1 THEN 'January'					WHEN 2 THEN 'February'					WHEN 3 THEN 'March'					WHEN 4 THEN 'April'					WHEN 5 THEN 'May'					WHEN 6 THEN 'June'					WHEN 7 THEN 'July'					WHEN 8 THEN 'August'					WHEN 9 THEN 'September'					WHEN 10 THEN 'October'					WHEN 11 THEN 'November'					WHEN 12 THEN 'December'				END; 

Month and day names are provided as a convenience and can be pulled in order to quickly display date-related strings in a variety of formats.

为了方便起见，提供了月和日名称，可以将其拉出，以便以各种格式快速显示与日期相关的字符串。

 SELECT @Day_of_Quarter = DATEDIFF(DAY, DATEADD(QUARTER, DATEDIFF(QUARTER, 0 , @Date_Counter), 0), @Date_Counter) + 1;SELECT @Day_of_Year = DATEPART(DAYOFYEAR, @Date_Counter);SELECT @Week_of_Month = DATEDIFF(WEEK, DATEADD(WEEK, DATEDIFF(WEEK, 0, DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date_Counter), 0)), 0), @Date_Counter ) + 1;SELECT @Week_of_Quarter = DATEDIFF(DAY, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, @Date_Counter), 0), @Date_Counter)/7 + 1; 

These statements perform a bit of more complex date math in order to determine the position of the date within its month, quarter, or year. The basis of these calculations is to calculate the start of a given year, week, or month, and then subtract the days or weeks from that point in time and the date being calculated. To emphasize the results, though, and their relative simplicity, the following TSQL results show the date, day of week, and day of week within the month for the start of 2017:

这些语句执行一些更复杂的日期数学运算，以确定日期在其月份，季度或年份中的位置。 这些计算的基础是计算给定年，周或月的开始，然后从该时间点和要计算的日期中减去天或周。 为了强调结果及其相对简单性，以下TSQL结果显示了2017年初的日期，星期几和一周中的星期几：

The results show that we are starting at the beginning of the month and breaking it into sets of seven. The same pattern holds true for the quarterly and yearly metrics.

结果表明，我们从月初开始，将其分为7组。 同样的模式适用于季度和年度指标。

 SELECT @First_Date_of_Week = DATEADD(DAY, -1 * @Day_of_Week + 1, @Date_Counter);SELECT @Last_Date_of_Week = DATEADD(DAY, 1 * (7 - @Day_of_Week), @Date_Counter);SELECT @First_Date_of_Month = DATEADD(DAY, -1 * DATEPART(DAY, @Date_Counter) + 1, @Date_Counter);SELECT @Last_Date_of_Month = EOMONTH(@Date_Counter);SELECT @First_Date_of_Quarter = DATEADD(QUARTER, DATEDIFF(QUARTER, 0, @Date_Counter), 0);SELECT @Last_Date_of_Quarter = DATEADD (DAY, -1, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, @Date_Counter) + 1, 0));SELECT @First_Date_of_Year = DATEADD(YEAR, DATEDIFF(YEAR, 0, @Date_Counter), 0);SELECT @Last_Date_of_Year = DATEADD(DAY, -1, DATEADD(YEAR, DATEDIFF(YEAR, 0, @Date_Counter) + 1, 0)); 

Determining the first and last dates in a given week, month, quarter, or year are similar calculations to positions in time. To figure out the first date in a period, we determine how far into the period we are and subtract that number of days. To determine the last date in a period, we do the opposite, subtracting the current date position from the period length. @Last_Date_of_Month can be determined with a built-in function, EOMONTH, which saves us a bit of work on that calculation.

确定给定星期，月份，季度或年份中的第一个和最后一个日期与时间位置的计算类似。 为了弄清一个时期的第一个日期，我们确定到该时期有多远，然后减去该天数。 要确定期间的最后日期，我们做相反的事情，即从期间长度中减去当前日期位置。 @Last_Date_of_Month可以使用内置函数EOMONTH来确定，这为我们节省了一些计算工作。

 SELECT @Day_of_Week_in_Month = (@Calendar_Day + 6) / 7;SELECT @Day_of_Week_in_Year = (@Day_of_Year + 6) / 7;SELECT @Day_of_Week_in_Quarter = (@Day_of_Quarter + 6) / 7; 

We can figure out the count of how many given days have occurred up to (and including) the date in question by taking the day number in the period, “rounding up” and dividing by seven (without a remainder). This breaks the period into weekly chunks, allowing us to understand where in the total period the current date falls.

我们可以通过计算期间中的天数，“四舍五入”并除以7（无余数），来算出到（包括）该日期为止已经发生了多少天。 这会将期间分为每周几段，使我们能够了解当前日期在整个期间内的位置。

 SELECT @Is_Leap_Year = CASE					WHEN @Calendar_Year % 4 <> 0 THEN 0					WHEN @Calendar_Year % 100 <> 0 THEN 1					WHEN @Calendar_Year % 400 <> 0 THEN 0					ELSE 1				END; SELECT @Days_in_Month = CASE					WHEN @Calendar_Month IN (4, 6, 9, 11) THEN 30				                                        WHEN @Calendar_Month IN (1, 3, 5, 7, 8, 10, 12) THEN 31					WHEN @Calendar_Month = 2 AND @Is_Leap_Year = 1 THEN 29					ELSE 28				END; 

The first BIT column determines if it is a leap year, which can help in knowing how many days are in the year, are in February, or if a leap year child actually gets a birthday in a given year. The second column uses the calendar month and that leap year bit to determine the number of days in the month, which prevents the need to figure it out later.

第一个BIT列确定是否是a年，这可以帮助您了解一年中的多少天，2月，或者a年的孩子在给定的年份实际上是生日。 第二列使用日历月和该leap年位来确定该月中的天数，从而避免了以后需要弄清楚的情况。

接下来是什么？ (What is Next?)

At this point we have defined all of the data elements we are looking for that can easily be determined up front, picked out data types, and completed those calculations. If you have any additional metrics to add, this is the place to do it. The process to do so is simple:

至此，我们已经定义了我们要寻找的所有数据元素，可以轻松地预先确定它们，挑选出数据类型并完成这些计算。 如果您要添加其他指标，则可以在此处进行。 这样做的过程很简单：

1. Dim_Date Dim_Date添加新列
2. Create a new local variable for the metric to be added.
   为要添加的指标创建一个新的本地变量。
3. Add a new calculation to assign the appropriate value into that variable.
   添加新的计算，以将适当的值分配给该变量。

Likewise, removing a column is the opposite process: Remove the column in Dim_Date, the local variable, and the subsequent calculation.

同样，删除列是相反的过程：删除局部变量Dim_Date和后续计算中的列。

Our next steps are:

我们的下一步是：

1. Dim_Date. Dim_Date的行。
2. Use set-based calculations to add holiday metrics to our data.
   使用基于集合的计算将假期指标添加到我们的数据中。
3. Run tests of the stored procedure in order to validate the results.
   运行存储过程的测试以验证结果。

结论 (Conclusion)

Calendar tables are extremely useful in any reporting, analytics, or even OLTP use case in which we need to frequently join data on data-related attributes. Not only can they greatly improve performance, but they simplify our code and allow reporting engines to consume that data with ease. As a bonus, we gain maintainability as we can retain a single copy of calendar data in one place. This reduces the likelihood of coding mistakes when operating on date data, especially when the calculations are complex.

日历表在任何报告，分析甚至OLTP用例中都非常有用，在这种情况下，我们需要频繁地将数据关联到与数据相关的属性上。 它们不仅可以极大地提高性能，而且可以简化我们的代码，并使报表引擎可以轻松使用该数据。 另外，由于可以将日历数据的一个副本保留在一个地方，因此我们具有可维护性。 这样可以减少对日期数据进行操作时出现编码错误的可能性，尤其是在计算复杂时。

Design a calendar table based on the needs of your application and add, remove, or adjust columns as needed. The metrics that matter most to one industry may be irrelevant to another. Completeness is key when creating a structure such as this, though. Consider what metrics you will need, both now and in the future. Also consider how much data you’ll need. Determine the minimum and maximum dates you’re going to need, and be ready to add or remove any when business needs change.

根据您的应用程序需求设计日历表，并根据需要添加，删除或调整列。 对一个行业最重要的指标可能与另一个行业无关。 但是，在创建这样的结构时，完整性是关键。 考虑现在和将来您将需要什么指标。 还考虑需要多少数据。 确定所需的最短日期和最长日期，并准备在业务需求发生变化时添加或删除任何日期。

Flexibility, performance, and maintainability are the primary gains to be had when using a calendar table. If you think of any interesting ideas that have not been mentioned here, feel free to share!

使用日历表时，灵活性，性能和可维护性是主要收益。 如果您有任何有趣的想法未在此处提及，请随时分享！

Next articles in this series:

本系列的下一篇文章：

参考资料和进一步阅读 (References and Further Reading)

Always check your results and make sure that calendar data is correct. It’s easy to update and replace, so do not hesitate to apply sufficient scrutiny as this data is intended to be used in many places.

始终检查结果，并确保日历数据正确。 它很容易更新和替换，因此不要犹豫，进行足够的审查，因为该数据打算在许多地方使用。

翻译自: