SQL Server date calculation
SQL Server date calculation
SQL Server date calculation
Generally, you need to obtain the current date and calculate some other dates. For example, your program may need to determine the first or last day of a month. Most of you probably know how to divide a date (year, month, day, and so on ), then, just use the split year, month, and day to calculate the date you need in several functions! In this article, I will show you how to use the DATEADD and DATEDIFF functions to calculate different dates that may be used in your program.
Before using the examples in this article, you must pay attention to the following issues. Most examples may not have different execution results on different machines, which is determined by the day of the week. The first day (DATEFIRST) determines the day your system uses as the first day of the week. All of the following examples are created on Sunday as the first day of the week, that is, the first day of the week is set to 7. If your first day settings are different, you may need to adjust these examples to make them consistent with the previous day settings. You can use the @ DATEFIRST function to check the settings on the first day.
To understand these examples, let's first review the DATEDIFF and DATEADD functions. The DATEDIFF function calculates the total number of time intervals between two dates, such as hour, day, week, month, and year. The DATEADD function calculates a date and obtains a new date by adding or subtracting the time interval. To learn more about the DATEDIFF and DATEADD functions and their time interval, read the Microsoft online help.
Using the DATEDIFF and DATEADD functions to calculate a date is a bit different from the method used to convert from the current date to the date you need. You must consider the time interval. For example, how many time intervals are there between the current date and the date you want to get, or between today and a certain day (such as-1-1. Understanding how to focus on Time Interval helps you easily understand my different date calculation examples.
The first day of a month
In the first example, I will show you how to get to the last day of the month from the current date. Note: This example and other examples in this article will only use the DATEDIFF and DATEADD functions to calculate the date we want. In each example, the date to be calculated is obtained through calculation but the previous time interval, and then addition or subtraction is performed.
This is the SQL script used to calculate the first day of a month:
Select dateadd (mm, DATEDIFF (mm, 0, getdate (), 0)
Let's split this statement to see how it works. The core function is getdate (). Most people know that this is a function that returns the current date and time. The DATEDIFF (mm, 0, getdate () function executed next is used to calculate the number of months between the current date and the date "00:00:00. 000. Remember: The period and time variables start from 00:00:00. 000, just like milliseconds. This is why you can specify the first time expression as "0" in the DATEDIFF function ". The next function is DATEADD, which adds the number of months from the current date to "1900-01-01. By adding the predefined date "" and the number of months of the current date, we can get the first day of this month. In addition, the time part of the calculated date will be "00:00:00. 000 ".
The calculation technique is to calculate the number of time intervals from the current date to "", and then add it to "" to obtain a special date, this technique can be used to calculate many different dates. The next example uses this technique to generate different dates from the current date.
Monday of this week
Here, we use the weekly (wk) interval to calculate the day of the week.
Select dateadd (wk, DATEDIFF (wk, 0, getdate (), 0)
The first day of a year
Now we use the interval of year (yy) to display the first day of the year.
Select dateadd (yy, DATEDIFF (yy, 0, getdate (), 0)
First day of the quarter
If you want to calculate the first day of the quarter, this example shows you how to do it.
Select dateadd (qq, DATEDIFF (qq, 0, getdate (), 0)
Midnight of that day
In the past, the getdate () function was used to cut off the time part in order to return the time value. It will take into account whether the current date is in the middle of the night. In this example, the DATEDIFF and DATEADD functions are used to obtain the midnight time point.
Select dateadd (dd, DATEDIFF (dd, 0, getdate (), 0)
Go deep into DATEDIFF and DATEADD function compute
You can understand that by using simple DATEDIFF and DATEADD functions, you can find many different dates that may be meaningful.
All the examples so far only calculate the number of time intervals between the current time and "", and then add it to the "" time interval to calculate the date. If you modify the number of time intervals, or use different time intervals to call the DATEADD function, or subtract the time interval instead of increasing, you can find and have different dates through these small adjustments.
Here are four examples of using another DATEADD function to calculate the last day to replace the two time intervals before and after the DATEADD function.
Last day of last month
This is an example of calculating the last day of the last month. It is obtained by subtracting 3 ms from the last day of the month. Remember that the time in SQL Server is accurate to 3 ms. That's why I need 3 milliseconds to get the date and time I want.
SELECT dateadd (MS,-3, DATEADD (mm, DATEDIFF (mm, 0, getdate (), 0 ))
The time part of the calculated date contains the time of the last day ("23: 59: 59: 997") that an SQL Server can record.
