Common SQL Server date comparison and date query statements: the first day of the month, the first day of the quarter, etc.

Address: http://www.cnblogs.com/aji88/archive/2010/10/21/1857296.html

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 "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.

1	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.

1	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.

1	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.

1	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 "1900-01-01", and then add it to the "1900-01-01" 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.

1	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.

Last day of last year

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.

1	SELECT  dateadd(ms,-3,DATEADD(yy,  DATEDIFF(yy,0,getdate()),  0))

 

Last day of this month

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. To modify this parameter, 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.

1	SELECT  dateadd(ms,-3,DATEADD(mm,  DATEDIFF(m,0,getdate())+1,  0))

 

Last day of this year

You should master this practice now. This is the script for calculating the last day of this year.

1	SELECT  dateadd(ms,-3,DATEADD(yy,  DATEDIFF(yy,0,getdate())+1,  0))。

 

The first Monday of this month

Now is the last example. Here I want to calculate the first Monday of this month. This is the computing script.

1	select  DATEADD(wk,  DATEDIFF(wk,0,  dateadd(dd,6-datepart(day,getdate()),getdate()) ),  0)

 

In this example, I used the "Monday of this week" script and made some modifications. The modified part is to replace the "getdate ()" part in the original script with the 6th day of this month, in the calculation, the current date is replaced with the 6th day of this month so that the calculation can get the first Monday of this month.

Summary

I hope these examples will give you some inspiration when you use the DATEADD and DATEDIFF functions to calculate the date. By using this mathematical method for calculating the time interval of a date, I found that useful calendars for displaying the interval between two dates are valuable. Note that this is only one way to calculate these dates. Remember, there are still many ways to get the same computing results. If you have other methods, it would be nice. If you do not have them, I hope these examples will give you some inspiration, when you use the DATEADD and DATEDIFF functions to calculate the date that your program may use.