学习SQL：使用日期和时间函数创建SQL Server报表-优快云博客

本文讲解了如何使用SQLServer的日期和时间函数创建复杂报表，包括生成日期范围和按分钟划分的时间间隔，适用于各种报告需求。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

In the previous two articles of this series, we’ve discussed how to create a report and how to use built-in date and time functions. Today, we’ll join these two together and analyze some interesting queries we could use while creating more complex SQL Server reports where date and time functions are used to create reporting categories.

在本系列的前两篇文章中，我们讨论了如何创建报告以及如何使用内置的日期和时间函数。今天，我们将两者结合在一起，并分析一些有趣的查询，这些查询可在创建更复杂SQL Server报表时使用，其中日期和时间函数用于创建报表类别。

数据模型和一些一般想法 (Data model and some general thoughts)

We’ll use the same data model we’re using throughout this series. Since we’ll be mainly focused on how to generate date (time) reporting categories, we’ll be using data from only one table and that is the call table.

在整个系列中，我们将使用相同的数据模型。由于我们将主要集中在如何生成日期（时间）报告类别上，因此我们将仅使用来自一个表（即调用表）的数据。

SQL Server Reports - create reporting intervals - the data model we'll use in the article

The main idea is to create several queries that could be used as they are in this article or easily tweaked to meet the requirements defined for the specific SQL Server report. We’ll use the statements and functions we’ve previously mentioned, but we’ll also introduce some new concepts in this article. These new concepts shall be covered only in the amount needed to understand SQL statements from this article, and we’ll talk more about them later in the series.

主要思想是创建几个查询，可以按本文中的方式使用这些查询，也可以对其进行轻松地调整以满足特定SQL Server报告所定义的要求。我们将使用前面提到的语句和函数，但在本文中还将介绍一些新概念。这些新概念仅涉及理解本文中SQL语句所需的数量，我们将在本系列后面的部分中进一步讨论它们。

为SQL Server报告创建日期范围（列出给定月份中的所有日期） (Creating a date range for a SQL Server report (list all dates in a given month))

One of the most common reporting requirements is to generate a report for the date range – including all dates in that range. E.g. let’s assume we want to list all calls in the given month. We want to have each date in that month together with the number of calls started on that date.

最常见的报告要求之一是针对日期范围（包括该范围内的所有日期）生成报告。例如，假设我们要列出给定月份中的所有通话。我们希望获得该月的每个日期以及该日期开始的呼叫数量。

You could suppose we can easily take calls from the call table and group them by date. And that’s OK. But the problem is that we may lack calls for some dates and for such dates we wouldn’t have a row in the final result. And we want to have this row present with the value of 0 (not only that the report looks nicer that way, but we also know that row isn’t missing as the result of an error, etc.).

您可以假设我们可以轻松地从呼叫表中接听电话并按日期对它们进行分组。没关系。但是问题在于，我们可能缺少某些日期的调用，而对于这些日期，最终结果中将没有一行。并且我们希望该行的值为0（不仅报表看起来更好，而且我们也知道该行不会由于错误等而丢失）。

To achieve that, we’ll need to create a list of all dates and then join that list on the query with the reporting data (the one selecting data from the call table).

为此，我们需要创建所有日期的列表，然后将该列表与查询数据（从调用表中选择数据）一起添加到查询中。

We could create a list of dates using the set of commands as presented below. Let’s take a look at these commands as well as their output.

我们可以使用如下所示的命令集创建日期列表。让我们看一下这些命令及其输出。

-- dates in range
-- declaring all DATE variables we'll use
DECLARE @date DATE;
DECLARE @start_date DATE;
DECLARE @end_date DATE;
DECLARE @loop_date DATE;
    
-- declaring a table variable
DECLARE @dates TABLE (date DATE);
    
-- setting the first and the last date in the month given by date
SET @date = '2020/05/12';
SET @start_date = DATEFROMPARTS(YEAR(@date ), MONTH(@date ), '01');
SET @end_date = EOMONTH(@date);
    
-- check dates
SELECT 
    @date  AS cur_date,
    @start_date AS first_date,
    @end_date AS last_date;
 
-- populating a table (variable) with all dates in a given month
SET @loop_date = @start_date;
WHILE @loop_date <= @end_date 
BEGIN
    INSERT INTO @dates(date) VALUES (@loop_date);
    SET @loop_date = DATEADD(DAY, 1, @loop_date);
END;
-- selecting report dates
SELECT * FROM @dates;

These are some important things I would like to point out related to the code presented above. I’ll start from the end, and that is the final result. Two queries returned two results:

这些是我要指出的与上述代码相关的重要事项。我将从头开始，这就是最终结果。两个查询返回两个结果：

The first query returned 3 values cur_date (given date), first_date (the first date in a month same as a month of the given date), and last_date (the last date in a month same as a month of the given date). This query was used only to show that we got the first and the last date in the month from the original date. Its’ result was used for the second query
第一个查询返回3个值cur_date（给定日期），first_date（与给定日期的月份相同的月份中的第一个日期）和last_date（与给定日期的月份相同的月份中的最后一个日期）。此查询仅用于表明我们从原始日期获得了该月的第一个和最后一个日期。其结果用于第二个查询
The second query returned all dates in the given month. Please notice that besides these dates we don’t have any other data
第二个查询返回给定月份中的所有日期。请注意，除了这些日期，我们没有其他数据

Now we’ll explain what we did to achieve this. Still, before we do it so, let’s mention a few new concepts we’ve used:

现在，我们将说明实现此目的的方法。尽管如此，在我们这样做之前，让我们先提到一些我们已经使用的新概念：

DECLARE @date DATE; declares a variable of the DATE type. Later we’ll assign value to this variable – SET @date = ‘2020/05/12’;, and use it in the code where needed. Variables are used (in databases, but also in programming) as a place to store values you’ll need later. In SQL Server (and generally speaking) you could define variables of any available data type used for columns
DECLARE @date DATE；声明一个DATE类型的变量。稍后，我们将为该变量赋值– SET @date ='2020/05/12'; ，并在需要的代码中使用它。变量（在数据库中以及在编程中）用作存储以后需要使用的值的地方。在SQL Server中（通常来说），您可以定义用于列的任何可用数据类型的变量
DECLARE @dates TABLE (date DATE); It is also possible to define the TABLE type variable. This variable shall be used to store a complete table, and can be used as a standard table – you can insert new data, or select from this variable (that is what we’ll do in the code), or perform other table-related operations
DECLARE @dates表（date DATE）；也可以定义TABLE类型变量。该变量将用于存储完整的表，并可用作标准表–您可以插入新数据，或从该变量中选择（这是我们将在代码中执行的操作），或执行其他与表相关的操作运作
We’ve used the WHILE loop. In programming, loops are used to execute a certain code as many times as loop condition is true. WHILE loop shall execute while the condition stands – that could be 0 times, only once, multiple times (the most common situation), or even an infinite number of times (this is usually the result of an error)
我们使用了WHILE循环。在编程中，使用循环执行特定代码的次数要满足循环条件为真的次数。 WHILE循环应在条件满足的情况下执行-可能是0次，仅一次，多次（最常见的情况），甚至是无数次（通常是错误的结果）

And this is what we did to achieve the result:

这就是我们为获得结果而做的事情：

First, we’ve defined a desired date -> SET @date = ‘2020/05/12’. This is the date in the month where we want to have all dates in a list
首先，我们定义了一个期望的日期-> SET @date ='2020/05/12' 。这是我们要在列表中包含所有日期的月份中的日期
With these two statements, we’ve defined the first and the last date in the given month -> SET @start_date = DATEFROMPARTS(YEAR(@date ), MONTH(@date ), ’01’); SET @end_date = EOMONTH(@date); For date and time functions, please check Learn SQL: SQL Server date and time functions article
通过这两个语句，我们定义了给定月份中的第一个和最后一个日期-> SET @start_date = DATEFROMPARTS（YEAR（@date），MONTH（@date），'01'）; SET @end_date = EOMONTH（@date）; 有关日期和时间函数，请查看“ 学习SQL：SQL Server日期和时间函数”文章
We’ve used the WHILE loop to go from the start date until the end date and inserted each date 1 by 1 in the table variable (INSERT INTO @dates(date) VALUES (@loop_date);). Please notice that we’ve increased date by 1 in each step/pass of the loop (SET @loop_date = DATEADD(DAY, 1, @loop_date);)
我们使用WHILE循环从开始日期到结束日期，并将每个日期1 依次插入表变量（ INSERT INTO @dates（date）VALUES（@loop_date）; ）中。请注意，在循环的每个步骤/遍历中，我们都将日期增加了1（ SET @loop_date = DATEADD（DAY，1，@loop_date）; ）
In the end, we’ve selected from the table variable to get the complete list
最后，我们从table变量中选择了完整的列表

The result of all of this is that for any given date, we can list all dates in that month from first to last. Later we could join these dates to actual report data and include in the report even dates without related reporting data.

所有这些的结果是，对于任何给定的日期，我们可以列出该月中从头到尾的所有日期。之后，我们可以将这些日期添加到实际报告数据中，甚至将没有相关报告数据的日期也包含在报告中。

Also, you could easily tweak this code (change the way how the @start_date and the @end_date are calculated) and list all dates e.g. in the given week; starting 20 days before a given date and ending 20 days after; in the given year, etc.

另外，您可以轻松地调整此代码（更改@start_date和@end_date的计算方式）并列出所有日期，例如在给定的一周中；从给定日期之前的20天开始，到之后的20天结束；在给定年份等

加入日期范围和报告数据 (JOIN a date range and the report data)

In the previous step, we’ve created a list of reporting dates we want to have present in the SQL Server report. Please notice that while creating this list we haven’t queried any database table. Now, we’ll join this list to the data from our database. The first thing to do is to check what is in the call table.

在上一步中，我们创建了要在SQL Server报表中显示的报表日期列表。请注意，在创建此列表时，我们没有查询任何数据库表。现在，我们将该列表与数据库中的数据连接起来。要做的第一件事是检查呼叫表中的内容。

SELECT * FROM call;

We can conclude we have 10 calls and all of them were made on 2020-01-11. We’ll use the code from the previous section of this article and combine it with the call table to create an actual report.

我们可以得出结论，我们有10个电话，所有这些电话都是在2020-01-11进行的。我们将使用本文上一部分中的代码，并将其与调用表结合以创建实际的报告。

-- select number of calls per day in the given month
DECLARE @date DATE;
DECLARE @start_date DATE;
DECLARE @end_date DATE;
DECLARE @loop_date DATE;
    
-- declaring a table variable
DECLARE @dates TABLE (date DATE);
    
-- setting the first and the last date in the month given by date
SET @date = '2020/01/12';
SET @start_date = DATEFROMPARTS(YEAR(@date ), MONTH(@date ), '01');
SET @end_date = EOMONTH(@date);
    
-- populating a table (variable) with all dates in a given month
SET @loop_date = @start_date;
WHILE @loop_date <= @end_date 
BEGIN
    INSERT INTO @dates(date) VALUES (@loop_date);
    SET @loop_date = DATEADD(DAY, 1, @loop_date);
END;
    
SELECT 
    d.date,
    COUNT(call.id) AS calls
FROM @dates d
LEFT JOIN call ON d.date = CAST(call.start_time AS DATE)
GROUP BY d.date;

Please notice two things:

请注意两件事：

^st column, but also a number of calls performed on each day in the 2¹列的日期相同的列表，而且在第^nd column (all of them are 0 except 10 calls on 2020-01-11) ²列的每一天进行呼叫的数量（所有的人都对2020年1月11日0除10个呼叫）
In the last query (SELECT), we’ve used LEFT JOIN to have all the dates present in the final report, COUNT(call.id) to count ids and not rows (counting rows would return 1 for each day where we have 0), used an alias name for the table variable – FROM @dates d, and casted/converted start time to DATE (it is originally DATETIME) – CAST(call.start_time AS DATE)
在上一个查询（SELECT）中，我们使用LEFT JOIN将所有日期显示在最终报告中，使用COUNT（call.id）来计算ID而不是行（计数行将在每天有0的每一天返回1）），为表变量使用别名– FROM @dates d ，并将开始时间强制转换/转换为DATE （最初为DATETIME）– CAST（call.start_time AS DATE）

以分钟为单位创建日期时间范围（作为报告类别） (Creating a datetime range in minutes (as a reporting category))

So far, we’ve only used dates as reporting categories. That is fine if we want our SQL Server report granulated on a daily level. But what to do if we want to create a report with different time intervals (hours, minutes, seconds, etc.)? Let’s first take a look at the code below.

到目前为止，我们仅使用日期作为报告类别。如果我们希望我们SQL Server报表每天细化，那就很好。但是，如果我们要创建具有不同时间间隔（小时，分钟，秒等）的报告怎么办？首先让我们看一下下面的代码。

-- number of calls with predefined start time, end time and interval
DECLARE @start_time DATETIME;	-- starting from here
DECLARE @end_time DATETIME;		-- until the time is under this value
DECLARE @interval CHAR(3);		-- interval definition (e.g. day, minute etc.)
DECLARE @increment INT;			-- interval increment 
DECLARE @loop_time DATETIME;	-- variable used in the loop
DECLARE @times TABLE(start_time DATETIME, end_time DATETIME);
    
SET @start_time = '2020-01-11 09:00:00';
SET @end_time = '2020-01-11 10:00:00';
SET @interval = 'MI';
SET @increment = 10;
    
SET @loop_time = @start_time;
    
WHILE @loop_time < @end_time 
BEGIN 
    IF @interval = 'yy' SET @loop_time = DATEADD(yy, @increment, @loop_time);	-- year
    IF @interval = 'qq' SET @loop_time = DATEADD(qq, @increment, @loop_time);	-- quarter
    IF @interval = 'mm' SET @loop_time = DATEADD(mm, @increment, @loop_time);	-- month
    IF @interval = 'dy' SET @loop_time = DATEADD(dy, @increment, @loop_time);	-- day of year
    IF @interval = 'dd' SET @loop_time = DATEADD(dd, @increment, @loop_time);	-- day
    IF @interval = 'wk' SET @loop_time = DATEADD(wk, @increment, @loop_time);	-- week
    IF @interval = 'dw' SET @loop_time = DATEADD(dw, @increment, @loop_time);	-- weekday
    IF @interval = 'hh' SET @loop_time = DATEADD(hh, @increment, @loop_time);	-- hour
    IF @interval = 'mi' SET @loop_time = DATEADD(mi, @increment, @loop_time);	-- minute
    IF @interval = 'ss' SET @loop_time = DATEADD(ss, @increment, @loop_time);	-- second
    IF @interval = 'ms' SET @loop_time = DATEADD(ms, @increment, @loop_time);	-- millisecond
    IF @interval = 'mcs' SET @loop_time = DATEADD(mcs, @increment, @loop_time);	-- microsecond
    IF @interval = 'ns' SET @loop_time = DATEADD(ns, @increment, @loop_time);	-- nanosecond
    INSERT INTO @times(start_time, end_time) VALUES (@start_time, @loop_time);
    SET @start_time = @loop_time;
END;
    
SELECT 
    t.start_time,
    t.end_time,
    COUNT(call.id) AS calls
FROM @times t
LEFT JOIN call ON t.start_time < call.start_time AND call.start_time <= t.end_time
GROUP BY 
    t.start_time,
    t.end_time;

SQL Server Reports - 10 minute intervals

The output of this report are 10 minutes time intervals on 2020-01-11, and the number of calls started during these intervals. Let’s see how we achieved that.

此报告的输出是在2020年1月11日的10分钟时间间隔，并且在这些间隔内开始的呼叫数。让我们看看我们是如何实现的。

The idea of the code above is to be able to define the start time and the end time of our SQL Server report, and intervals. We’ll define intervals by time unit and the increment. In our example time units are minutes and the increment value is set to 10. Let’s quickly analyze our code:

上面代码的思想是能够定义SQL Server报告的开始时间和结束时间以及时间间隔。我们将按时间单位和增量来定义时间间隔。在我们的示例中，时间单位是分钟，增量值设置为10。让我们快速分析一下代码：

DECLARE @start_time DATETIME; and DECLARE @end_time DATETIME; – are variables where we’ll store/define the start time and the end time of our SQL Server report
DECLARE @start_time DATETIME; 和DECLARE @end_time DATETIME； –是用于存储/定义SQL Server报告的开始时间和结束时间的变量
DECLARE @interval CHAR(3); – is used to store a 2 or 3 letters code defining the interval unit (minute, second, hour, etc.)
声明@interval CHAR（3）; –用于存储定义间隔单位（分钟，秒，小时等）的2或3个字母代码
DECLARE @increment INT; – is used to store the number of units per each interval
十进制@increment INT; –用于存储每个间隔的单位数
We’ve again used the WHILE loop to loop from start time to end time. At each step of the loop, we’ve checked the interval defined and added interval accordingly, inserted one row in the table variable, and increased value for the next loop pass
我们再次使用WHILE循环从开始时间到结束时间循环。在循环的每个步骤中，我们都检查了定义的间隔并相应地增加了间隔，在表变量中插入了一行，并为下一个循环遍历增加了值
The last statement is the SELECT statement using the table variable and the call table, to count the number of calls in the intervals defined in the table @times
最后一条语句是使用表变量和调用表的SELECT语句，用于计算表@times中定义的间隔中的调用次数

It’s worth noticing that you can easily change the interval unit and increment and adjust this code accordingly to your reporting needs.

值得注意的是，您可以轻松更改间隔单位并增加并根据您的报告需要调整此代码。

何时使用这些查询（在SQL Server报告中） (When to use these queries (in SQL Server reports))

I’ve been many times in situations where I’ve needed to create reports for a certain time period. In case you need to do that, you can use the queries above to create a date/datetime categories for your SQL Server report. Of course, you’ll need to tweak queries a little and add your reporting tables (SELECTs) but that should work perfectly.

在需要在特定时间段内创建报告的情况下，我曾经历过很多次。如果需要这样做，可以使用上面的查询为您SQL Server报告创建日期/日期时间类别。当然，您需要稍微调整一下查询并添加您的报告表（SELECT），但这应该能很好地工作。