So far, we haven’t talked about SQL Server date and time functions. Today we’ll change that. We’ll take a close look at the ones most frequently used and mention all other date and time functions as well. This will be also the first step to create reports, including date and time functions. We’ll do that in upcoming articles in this series.
到目前为止，我们还没有讨论SQL Server日期和时间函数。 今天我们将改变它。 我们将仔细研究最常用的功能，并提及所有其他日期和时间功能。 这也是创建包括日期和时间功能的报告的第一步。 我们将在本系列的后续文章中进行介绍。
数据模型和一些一般想法 (Data model and some general thoughts)
The data model we’ll use is the same one we’re using in this series. We won’t actually use any data from the tables. I just want to point out that we’re using date and datetime data types in our model. Some of them are the result of storing the real-life data (next_call_date), while others are generated automatically by the system when data is inserted (ts_inserted). This shall usually be the case in most models as well.
我们将使用的数据模型与本系列中使用的数据模型相同。 我们实际上不会使用表中的任何数据。 我只想指出，我们在模型中使用日期和日期时间数据类型。 其中一些是存储实际数据（next_call_date）的结果，而其他一些是在插入数据时（ts_inserted）由系统自动生成的。 大多数模型通常也是如此。
We’ve mentioned that we’ll talk about SQL Server date and time functions. As their name says, they are functions and they work the same way as user-defined functions work. You can read more about it in this article. If we would like to explain this in the simplest possible manner, a function takes a number of parameters (could be none, 1 or more than 1) and returns the value. A function won’t have parameters only if it returns a system value (reading from a predefined source). Some of these functions will transform date & time data types into integers or strings, while others will do the opposite. Also, there will be format changes between different date and time data types.
我们已经提到过，我们将讨论SQL Server的日期和时间函数。 顾名思义，它们是函数，它们的工作方式与用户定义的函数相同。 您可以在本文中阅读有关它的更多信息。 如果我们想以最简单的方式来解释这一点，则一个函数接受多个参数（可以是无，1或大于1）并返回值。 仅当函数返回系统值（从预定义的源读取）时，它才会具有参数。 其中一些功能会将日期和时间数据类型转换为整数或字符串，而其他功能则相反。 此外，不同日期和时间数据类型之间的格式也会发生变化。
SQL Server日期和时间数据类型 (SQL Server date and time data types)
Before moving to functions, we’ll need to mention the date and time data types these functions work with. As this is the case with all data types, they are just a different way SQL Server interprets the set of 0s and 1s stored in the memory. This interpretation allows us to store different types of information with different precision. This happens when we’re talking about numbers, times, more generally about values that are stored as decimal numbers in real life. Since you can insert another decimal number between any two decimal numbers, each such value is indefinite and we need to make an approximation.
在转到函数之前，我们需要提及这些函数使用的日期和时间数据类型。 由于所有数据类型都是这种情况，它们只是SQL Server解释存储在内存中的0和1集的一种不同方式。 这种解释使我们能够以不同的精度存储不同类型的信息。 当我们谈论数字，时间，更普遍地说是关于在现实生活中存储为十进制数字的值时，就会发生这种情况。 由于您可以在任意两个十进制数字之间插入另一个十进制数字，因此每个这样的值都是不确定的，我们需要进行近似计算。
- Note: 注意： The number of bytes (bits) used for a certain data type determines the range of possible values we can store, as well the precision for types which are used to approximate decimal number用于某种数据类型的字节（位）数决定了我们可以存储的可能值的范围，以及用于近似十进制数的类型的精度
Let’s now list all SQL Server date and time data types (starting with the most commonly used):
现在让我们列出所有SQL Server日期和时间数据类型（从最常用的数据开始）：
- date – format is YYYY-MM-DD; stores values from 0001-01-01 to 9999-12-31; with the accuracy of 1 day (there is no approximation here because acts same as integer values); uses 3 bytes 日期 –格式为YYYY-MM-DD； 存储从0001-01-01到9999-12-31的值; 精度为1天（此处近似值，因为与整数值相同）； 使用3个字节
- datetime –format is YYYY-MM-DD hh:mm:ss[.nnn]; stores values from 1753-01-01 to 9999-12-31; with the accuracy of 0.00333 seconds (please notice we have approximation here); uses 8 bytes datetime –格式为YYYY-MM-DD hh：mm：ss [.nnn]; 存储从1753-01-01到9999-12-31的值; 精确度为0.00333秒（请注意，此处为近似值）； 使用8个字节
- time – format is hh:mm:ss[.nnnnnnn]; stores values from 00:00:00.0000000 to 23:59:59.9999999; with the accuracy of 100 nanoseconds; uses 3 to 5 bytes 时间 –格式为hh：mm：ss [.nnnnnnn]; 存储从00：00：00.0000000到23：59：59.9999999的值; 精度为100纳秒； 使用3到5个字节
- smalldatetime – format is YYYY-MM-DD hh:mm:ss; stores values from 1900-01-01 to 2079-06-06; with the accuracy of 1 minute; uses 4 bytes smalldatetime –格式为YYYY-MM-DD hh：mm：ss; 存储从1900-01-01到2079-06-06的值; 1分钟的精度； 使用4个字节
- datetime2 –format is YYYY-MM-DD hh:mm:ss[.nnnnnnn]; stores values from 0001-01-01 00:00:00.0000000 to 9999-12-31 23:59:59.9999999; with the accuracy of 100 nanoseconds; uses 6 to 8 bytes datetime2 –格式为YYYY-MM-DD hh：mm：ss [.nnnnnnn]; 存储从0001-01-01 00：00：00.0000000到9999-12-31 23：59：59.9999999的值; 精度为100纳秒； 使用6到8个字节
- datetimeoffset – format is YYYY-MM-DD hh:mm:ss[.nnnnnnn]; stores values from 0001-01-01 00:00:00.0000000 to 9999-12-31 23:59:59.9999999; with the accuracy of 100 nanoseconds; uses 8 to 10 bytes datetimeoffset –格式为YYYY-MM-DD hh：mm：ss [.nnnnnnn]; 存储从0001-01-01 00：00：00.0000000到9999-12-31 23：59：59.9999999的值; 精度为100纳秒； 使用8到10个字节
In most cases, you’ll use either datetime, either date. The remaining 4 types are here if you want to have higher accuracy (datetime2, datetimeoffset), lower accuracy (smalldatetime), or store only time (time).
在大多数情况下，您将使用datetime或date 。 如果要具有较高的精度（ datetime2 ， datetimeoffset ），较低的精度（ smalldatetime ）或仅存储时间（ time ），则在此处剩下的4种类型。
常用SQL Server日期和时间函数 (Frequently used SQL Server date and time functions)
Similarly to date and time data types, some SQL Server date and time functions are used more often, while some are used rarely. In this part, we’ll check these that are used often, and you can expect you’ll need them in many situations.
与日期和时间数据类型类似，某些SQL Server日期和时间函数使用较多，而有些则很少使用。 在本部分中，我们将检查经常使用的这些，您可以期望在许多情况下都需要它们。
If you want to get system values, you’ll use some of the following:
如果要获取系统值，请使用以下一些方法：
SELECT GETDATE() AS _GETDATE;
SELECT SYSDATETIME() AS _SYSDATETIME;
SELECT CURRENT_TIMESTAMP AS _CURRENT_TIMESTAMP;
GETDATE() and CURRENT_TIMESTAMP return the datetime value from the server where SQL Server runs.
GETDATE（）和CURRENT_TIMESTAMP从运行SQL Server的服务器返回datetime值。
SYSDATETIME() returns the same as the previous 2, but with the greater precision, so the result returned is of the datetimeoffset(7).
SYSDATETIME（）返回与前2个相同的结果，但精度更高，因此返回的结果为datetimeoffset（7）。
Besides system date and time functions, we have several other important functions.
除了系统日期和时间功能，我们还有其他几个重要功能。
The next important set of functions is the one containing functions that return date parts. Let’s take a look at the following statements and their result.
下一组重要的函数是包含返回日期部分的函数的一组函数。 让我们看一下以下语句及其结果。
SELECT
YEAR('2020/05/01 14:38:52') AS _year,
MONTH('2020/05/01 14:38:52') AS _month,
DAY('2020/05/01 14:38:52') AS _day;
SELECT
DATEPART(YEAR, '2020/05/01 14:38:52') AS _year,
DATEPART(MONTH, '2020/05/01 14:38:52') AS _month,
DATEPART(DAY, '2020/05/01 14:38:52') AS _day,
DATEPART(HOUR, '2020/05/01 14:38:52') AS _hour,
DATEPART(MINUTE, '2020/05/01 14:38:52') AS _minute,
DATEPART(SECOND, '2020/05/01 14:38:52') AS _second;
SELECT
DATENAME(YEAR, '2020/05/01 14:38:52') AS _year,
DATENAME(MONTH, '2020/05/01 14:38:52') AS _month,
DATENAME(DAY, '2020/05/01 14:38:52') AS _day,
DATENAME(HOUR, '2020/05/01 14:38:52') AS _hour,
DATENAME(MINUTE, '2020/05/01 14:38:52') AS _minute,
DATENAME(SECOND, '2020/05/01 14:38:52') AS _second;
YEAR(…), MONTH(…), and DAY(…) return related parts of the given date.
YEAR（…），MONTH（…）和DAY（…）返回给定日期的相关部分。
DATEPART(date_part, date) and DATENAME(date_part, date) both return parts of the date. DATEPART returns them as integer values, while DATENAME returns them as strings. Please notice that you can define more than just a year, month or day. All date_part values you can define are: year(yy, yyyy); quarter(qq, q); month(mm, m); dayofyear(dy, y); day(dd, d); week(wk, ww); weekday(dw); hour(hh); minute(mi, n); second(ss, s); millisecond(ms); microsecond(mcs); nanosecond(ns); TZoffset(tz); ISO_WEEK(isowk, isoww).
DATEPART（日期部分，日期）和DATENAME（日期部分，日期）都返回日期的一部分。 DATEPART以整数值返回它们，而DATENAME以字符串形式返回它们。 请注意，您可以定义的不仅仅是一年，一个月或一天。 您可以定义的所有date_part值是：year（yy，yyyy）; 季度（qq，q）; 月（毫米，米）; dayofyear（dy，y）; 天（dd，d）; 周（周，周）； 工作日（dw）; 小时（hh）; 分钟（mi，n）; 秒（ss，s）; 毫秒（ms）; 微秒（mcs）; 纳秒（ns）; TZoffset（tz）; ISO_WEEK（isowk，isoww）。
The next 3 functions are used to create date or modify/combine dates. Let’s take a look at them.
接下来的3个功能用于创建日期或修改/合并日期。 让我们看看它们。
SELECT DATEFROMPARTS(2020,5,1) AS _date;
SELECT DATEADD(DAY, -10, '2020-05-01') AS _date_add;
SELECT DATEDIFF(DAY, '2020-05-01', '2020-05-10') AS _date_difference;
DATEFROMPARTS(year, month, day) takes a year, month and day as integer values and creates 1 date out of them.
DATEFROMPARTS（年，月，日）以年，月和日为整数值，并从中创建1个日期。
DATEADD(date_part, interval, date) takes 3 arguments and returns a date that is interval (date_parts) number of given units (date_part) distant from the given date (date).
DATEADD（date_part，interval，date）接受3个参数，并返回一个日期，该日期是与给定日期（date）相距的给定单位（date_part）的间隔（date_parts）数。
DATEDIFF(date_part, start_date, end_date) returns the number of units (date_part) between end_date and start_date (end_date – start_date).
DATEDIFF（date_part，start_date，end_date）返回介于end_date和start_date（end_date – start_date）之间的单位数（date_part）。
This is the end of my selection of the most commonly used SQL Server date and time functions. These functions should solve most of your “problems”. Still, there are some more, and we’ll cover them now.
这是我选择最常用SQL Server日期和时间函数的结尾。 这些功能应该可以解决您的大多数“问题”。 不过，还有更多内容，我们现在将进行介绍。
不常用SQL Server日期和时间函数 (Less frequently used SQL Server date and time functions)
Let’s mention the less frequently used SQL Server date and time functions.
让我们谈谈不太常用SQL Server日期和时间函数。
We’ll start with GETDATE() and SYSDATETIME() counterparts. Let’s take a look at the following statements:
我们将从对应的GETDATE（）和SYSDATETIME（）开始。 让我们看一下以下语句：
SELECT GETUTCDATE() _GETUTCDATE;
SELECT SYSUTCDATETIME() _SYSUTCDATETIME;
SELECT SYSDATETIMEOFFSET() AS _SYSDATETIMEOFFSET;
GETUTCDATE() acts the same as GETDATE() and CURRENT_TIMESTAMP but it returns the UTC datetime value.
GETUTCDATE（）与GETDATE（）和CURRENT_TIMESTAMP的作用相同，但它返回UTC日期时间值。
SYSUTCDATETIME() acts the same as SYSDATETIME() but it returns UTC values.
SYSUTCDATETIME（）与SYSDATETIME（）的作用相同，但它返回UTC值。
SYSDATETIMEOFFSET() is the same as SYSUTCDATETIME() but besides it also returns the time zone offset.
SYSDATETIMEOFFSET（）与SYSUTCDATETIME（）相同，但是它还返回时区偏移量。
- Note: This note is completely unrelated to date and time functions. Please notice that in the first 2 statements I haven’t used AS, while in 3rd I did it – with the same output (alias name had been used)
- 注意： 此注释与日期和时间功能完全无关。 请注意，在第2条语句我没有AS使用，而在第三我做到了-有相同的输出（别名已被使用）
Another interesting function is EOMONTH.
另一个有趣的功能是EOMONTH。
SELECT EOMONTH('2020-05-01') AS _eom;
For the given date, it returns the last date in this month. This proves to be very useful in several situations.
对于给定的日期，它将返回该月的最后一个日期。 在几种情况下，这被证明是非常有用的。
I’ll list the remaining functions without giving examples:
我将列出其余功能而不给出示例：
- SWITCHOFFSET – Preserves the UTC value while changing the time zone to the one of a DATETIMEOFFSET value SWITCHOFFSET –在将时区更改为DATETIMEOFFSET值之一的同时保留UTC值
- TODATETIMEOFFSET – Changes type from DATETIME2 into a DATETIMEOFFSET TODATETIMEOFFSET –将类型从DATETIME2更改为DATETIMEOFFSET
- DATETIME2FROMPARTS – Creates and returns the DATETIME2 from the given date and time parts DATETIME2FROMPARTS –从给定的日期和时间部分创建并返回DATETIME2
- DATETIMEOFFSETFROMPARTS – Creates and returns the DATETIMEOFFSET from the given date and time parts DATETIMEOFFSETFROMPARTS –从给定的日期和时间部分创建并返回DATETIMEOFFSET
- TIMEFROMPARTS – Does the same as what DATEFROMPARTS does for the date, but for time. So, it creates a TIME from the given parts TIMEFROMPARTS –与DATEFROMPARTS的日期相同，但时间相同。 因此，它根据给定的部分创建一个TIME
- ISDATE – Is used to check if a given value is a valid datetime, date, or time, value ISDATE –用于检查给定值是否为有效的日期时间，日期或时间值
结论 (Conclusion)
The first thing we want to do while working with databases is to store our real-world (business) data. In most cases, they’ll contain date and time values. Still, we can also expect that we’ll need to store other date and time values, e.g. when data had been inserted or updated. Working with SQL Server databases without using SQL Server date and time functions is almost impossible. Therefore, store link to this article somewhere to remind yourself of these functions (in case you’re stuck while working with dates & times).
在使用数据库时，我们要做的第一件事是存储我们的真实（业务）数据。 在大多数情况下，它们将包含日期和时间值。 尽管如此，我们也可以预期我们将需要存储其他日期和时间值，例如何时插入或更新数据。 在不使用SQL Server日期和时间功能的情况下使用SQL Server数据库几乎是不可能的。 因此，请将本文的链接存储在某处，以提醒您自己这些功能（以防在处理日期和时间时陷入困境）。
In the upcoming article, we’ll use functions presented in this article to create report categories and reports. Stay tuned!
在下一篇文章中，我们将使用本文介绍的功能来创建报告类别和报告。 敬请关注！
目录 (Table of contents)
翻译自: https://www.sqlshack.com/learn-sql-sql-server-date-and-time-functions/
扫一扫
945
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
