SQL Server date calculation SQL statement summary (1/2)

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.

Let's first review the DATEDIFF and DATEADD functions.

• The DATEDIFF function calculates the total number of time intervals between two dates, including hours, days, weeks, months, and years.
• 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.

Calculate the SQL script for the first day of a month:

The code is as follows:	Copy code
Select dateadd (mm, DATEDIFF (mm, 0, getdate (), 0)

Script explanation: Let's split this statement to see how it works. The core function is getdate (). This is the 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.

• The SQL script for Monday of this week:

The code is as follows:	Copy code
Select dateadd (wk, DATEDIFF (wk, 0, getdate (), 0)

Here, we use the weekly (wk) interval to calculate the day of the week.

• SQL script for the first day of a year:

The code is as follows:	Copy code
Select dateadd (yy, DATEDIFF (yy, 0, getdate (), 0)

Use the interval of year (yy) to display the first day of the year.

• SQL script for the first day of the quarter:

The code is as follows:	Copy code
Select dateadd (qq, DATEDIFF (qq, 0, getdate (), 0)

 

• SQL scripts for the middle of the night:

The code is as follows:	Copy code
Select dateadd (dd, DATEDIFF (dd, 0, getdate (), 0)

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.

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's SQL script: SELECT dateadd (MS,-3, DATEADD (mm, DATEDIFF (mm, 0, getdate (), 0 ))
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.

The time part of the calculated date contains the time at the last time of the day that an SQL Server can record.

• Last Day of SQL script: SELECT dateadd (MS,-3, DATEADD (yy, DATEDIFF (yy, 0, getdate (), 0 ))
In the above example, to get the last day of last year, you need to subtract 3 MS from the first day of this year.

• SQL script for the last day of the month: SELECT dateadd (MS,-3, DATEADD (mm, DATEDIFF (m, 0, getdate () + 1, 0 ))
Now, in order to get the last day of this month, I need to slightly modify the statements to get the last day of the previous month. For modification, you need to use DATEDIFF to compare the time interval returned by the current date and "" with 1. By adding a month, I calculate the first day of the next month, and then subtract 3 milliseconds, so that the last day of the month is calculated. This is the SQL script used to calculate the last day of the month.

Homepage 1 2 Last page