Shulou(Shulou.com)06/01 Report--

This article mainly explains "how to use DATEADD in SQL Server". The content in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn how to use DATEADD in SQL Server.

Syntax

Like similar functions, DATEADD can perform arithmetic operations on dates and times. The syntax is simple:

DATEADD (datepart, number, date)

The number part must be an integer and must be within the range of acceptable values for the date part.

The datepart section must be one of the following date sections (we saw it in previous posts):

DATEPART acronym year Yes, yyyy25 coin qq, Q-month mm,mDAYOFYEARdy, y-day dd, d-week wk,ww weekdays dw,w hours HH minutes mi,n second ss,s milliseconds lady microseconds MCS nanosecond NS

Although DATEADD supports the abbreviations shown in the table above, we should make every effort to use complete expressions to ensure the clarity of the code. If we use abbreviations, SQL Server will not run faster.

Also note that although we can add or subtract DATEADD in nanoseconds, the minimum granularity of the DATETIME2 data type is 100 nanoseconds, so rounding needs to be considered.

Return type

DATEADD will return the result using the data type used in the date parameter. For example, if we use a text string that represents a date in YYYYMMDD format, the return type will be a DATETIME value because the text string is implicitly converted to DATETIME.

SELECT DATEADD (DAY,1,'20181031')-returns the DATETIME value of '2018-11-01 00purl 00.000'

However, if we enter a value using DATETIME2, the result will be a DATETIME2 value.

SELECT DATEADD (nanosecond, 100th cast ('20181031' AS DATETIME2)-returns the DATETIME2 value of '2018-10-31 00001' plus or minus 00.0000001'

We have seen before that DATEADD can be used for addition and subtraction, which makes it easy to calculate values backward and forward. Let's assume that we need to calculate the time point 100 days ago. If we take today as a starting point, it will look like this:

DECLARE @ dt DATETIME2 = SYSUTCDATETIME (); SELECT @ dt AS [TimeNow], DATEADD (DAY,-100 @ dt) AS [TimeThen]

Note the use of the minus sign number in the section. The results are as follows:

TimeNow: 2018-10-310917purl 21.7866500

TimeThen: 2018-07-23 0917purl 21.7866500

Months of arithmetic

One last thought about this feature. When increasing or decreasing the number of months, note that months do not include 31 days. For example, let's add a month at the end of February 2018:

SELECT DATEADD (MONTH,1,'20180228')-returns the data value '20180328'

However, if we add one, two or three months at the end of January 2018, we will see different results:

SELECT DATEADD (MONTH,1,'20180131');-returns the data value '20180228'

SELECT DATEADD (MONTH,2,'20180131');-returns the data value '20180331'

SELECT DATEADD (MONTH,3,'20180131');-returns the data value '20180430'

DATEADD is a very useful system function to add and subtract date and time values from T-SQL, which I use widely. As long as we remember its quirks around data types and months of length, it will be very powerful.

Thank you for your reading, the above is the content of "how to use DATEADD in SQL Server". After the study of this article, I believe you have a deeper understanding of how to use DATEADD in SQL Server, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.