Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to achieve date calculation in SQL Server. The content of the article is of high quality, so the editor shares it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

Usually, you need to get the current date and calculate some other dates. For example, your program may need to determine the first or last day of the month. Most of you probably know how to divide the date (year, month, day, etc.), and then just use the split year, month, day, etc., in a few functions to calculate the date you need! In this article, I'll show you how to use the DATEADD and DATEDIFF functions to calculate some of the different dates you might use in your program. Before using the examples in this article, you must pay attention to the following problems. Most of the examples may not have the same results on different machines, depending on which day is the first day of the week. The DATEFIRST setting determines which day your system uses as the first day of the week. All the following examples are created with Sunday as the first day of the week, that is, the first day is set to 7. If your first day settings are different, you may need to adjust these examples to match the different first day settings. You can check the first day settings through the @ @ DATEFIRST function. To understand these examples, let's review the DATEDIFF and DATEADD functions. The DATEDIFF function calculates the total number of hours, days, weeks, months, years, and so on, between two dates. The DATEADD function calculates a date to get a new date by adding or subtracting the interval. To learn more about DATEDIFF and DATEADD functions and intervals, you can read the Microsoft online help. Using the DATEDIFF and DATEADD functions to calculate the date is a little different from the way you would have thought about converting from the current date to the date you need. You must consider it in terms of time interval. For example, how much time is there between the current date and the date you want to get, or between today and a certain day (such as 1900-1-1), and so on. Understanding how to focus on time intervals will help you easily understand my different date calculation examples. For the first example of the first day of a month, I will show you how to get to the last day of the month from the current date. Please note that this and other examples in this article will only use the DATEDIFF and DATEADD functions to calculate the dates we want. Each example will calculate the desired date by calculating the previous time interval, and then adding and subtracting. This is the SQL script that calculates the first day of the month: SELECTDATEADD (mm,DATEDIFF (mm,0,getdate ()), 0) Let's break this statement down to see how it works. The core function is getdate (), which most people know is a function that returns the current date and time. The next function to be executed, DATEDIFF (mm,0,getdate ()), is to calculate the number of months between the current date and the date "1900-01-0100". Remember: the period and time variables, like milliseconds, are calculated from "1900-01-0100". This is why you can specify the first time expression as "0" in the DATEDIFF function. The next function is DATEADD, increasing the number of months from the current date to "1900-01-01". By increasing the predefined date "1900-01-01" and the number of months of the current date, we can get the first day of the month. In addition, the time portion of the calculated date will be "000.000". The trick of this calculation is to first calculate the interval between the current date and "1900-01-01", and then add it to "1900-01-01" to get a special date. This technique can be used to calculate many different dates. The next example also uses this technique to generate different dates from the current date. This week's Monday here I use the wk interval to calculate which day is this week's Monday. SELECTDATEADD (wk,DATEDIFF (wk,0,getdate ()), 0) the first day of the year now uses the interval of the year (yy) to show the first day of the year. The first day of the SELECTDATEADD (yy,DATEDIFF (yy,0,getdate ()), 0) quarter if you want to calculate the first day of the quarter, this example tells you what to do. SELECTDATEADD (qq,DATEDIFF (qq,0,getdate ()), 0) once needed to truncate the time part through the getdate () function in order to return the time value, it will take into account whether the current date is in the middle of the night. If so, this example uses the DATEDIFF and DATEADD functions to get the midnight time. SELECTDATEADD (dd,DATEDIFF (dd,0,getdate ()), 0) goes deep into the calculation of DATEDIFF and DATEADD functions. You can see that by using simple DATEDIFF and DATEADD functions, you can find many different dates that may be meaningful. All the examples so far just calculate the number of intervals between the current time and "1900-01-01", and then add it to the "1900-01-01" interval to calculate the date. Suppose you change the number of intervals, or use different intervals to call the DATEADD function, or subtract the intervals instead of increasing them, then you can find and many different dates through these small adjustments. Here are four examples that use another DATEADD function to calculate the time interval before and after the last day to replace the DATEADD function. The last day of last month this is an example of calculating the last day of last month. It is obtained by subtracting 3 milliseconds from the last day of the month. One thing to keep in mind is that in SqlServer the time is accurate to 3 milliseconds. That's why I need to subtract 3 milliseconds to get the date and time I want. The time portion of the date calculated by SELECTdateadd (ms,-3,DATEADD (mm,DATEDIFF (mm,0,getdate (), 0)) contains the time of the last moment of the day that SqlServer can record. In order to get the last day of last year, you need to subtract 3 milliseconds from the first day of this year. SELECTdateadd (ms,-3,DATEADD (yy,DATEDIFF (yy,0,getdate (), 0) the last day of this month now, in order to get the last day of this month, I need to modify the statement to get the last day of last month slightly. The modification requires adding 1 to the current date compared with the time interval returned by "1900-01-01" with DATEDIFF. By adding one month, I calculate the first day of the next month, and then subtract 3 milliseconds, thus calculating the last day of the month. This is the SQL script that calculates the last day of the month. SELECTdateadd (ms,-3,DATEADD (mm,DATEDIFF (yy,DATEDIFF (yy,0,getdate ()) + 1) on the last day of the year, you should now master this practice, which is to calculate the last day of the year script SELECTdateadd (yy,DATEDIFF (yy,0,getdate ()) + 1 (0)). The first Monday of this month is all right, and now is the last example. Here I'm going to calculate the first Monday of the month. This is a calculation script. SelectDATEADD (wk,DATEDIFF (wk,0, dateadd (dd,6-datepart (day,getdate ()), getdate (), 0) in this example, I used the "Monday of this week" script with a little modification. The modified part is to replace the "getdate ()" part of the original script with the 6th day of the month, and replace the current date with the 6th day of the month so that the calculation can get the first Monday of the month. To sum up, I hope these examples will give you some inspiration when you use the DATEADD and DATEDIFF functions to calculate dates. By using this mathematical method of calculating the time interval of a date, I find it valuable to show a useful calendar for the interval between two dates. Note that this is just one way to calculate these dates. Keep in mind that there are many ways to get the same results. If you have other methods, that's good, and if you don't, I hope these examples will give you some inspiration when you want to use the DATEADD and DATEDIFF functions to calculate the dates that your program might use. Appendix Other date processing methods 1) get rid of declare@datetime set@=getdate ()-- '2003-7-110 declare@datetime set@=getdate 00' SELECT@,DATEADD (day,DATEDIFF (day,0,@), 0) 2) display the day of the week selectdatename (weekday,getdate ()) 3) how to get the number of days of a month declare@mint set@m=2-- month selectdatediff (day) In addition, '2003-'+cast (@ masvarchar) +'-15 years of cinematography 2003 murders cast (@ m+1asvarchar) +'- 15') Get the number of days of the month selectdatediff (day,cast (month (GetDate ()) asvarchar) +'-'+ cast (month (GetDate ()) asvarchar) +'- 15 days asvarchar (month (GetDate ()) asvarchar) +'-'+ cast (month (GetDate ()) + 1asvarchar) +'- 15') or use a script that calculates the last day of the month Then use the last day of the DAY function area, SELECTDay (dateadd (ms,-3,DATEADD (mm,DATEDIFF (mpje) getdate () + 1) 4) to determine whether it is a leap year: SELECTcaseday (mm,2,dateadd (ms,-3,DATEADD (yy,DATEDIFF (yy,0,getdate (), 0) when28then' normal year 'else' leap year' end or selectcasedatediff (day,datename (year,getdate ()) +'- 02-01') Dateadd (mm,1,datename (year,getdate ()) +'- 02-01') when28then' normal year 'else' leap year' end 5) how many days in a quarter declare@mtinyint,@timesmalldatetime select@m=month (getdate ()) select@m=casewhen@mbetween1and3then1 when@mbetween4and6then4 when@mbetween7and9then7 else10end select@time=datename (year,getdate ()) +'-'+ convert (varchar (10), @ m) +'- 01' selectdatediff (day,@time) Dateadd (mm,3,@time))

On how to achieve date calculation in SQL Server to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.