SQL statement used to query the employee list for a birthday in a certain period of time

First, let's talk about the two database functions to be used: dateadd and datediff.

Dateadd function: dateadd (interval, number, date) returns a date data, and the three parameters of the function are required.
Interval indicates that the interval can be YY (year), mm (month), DD (day), QQ (quarter), and WW (week );
Number indicates the value of the interval. A positive number indicates the future of the date data, and a negative number indicates the former of the big feature of the date data.
Date is the date data. Generally, it is a date field in the data table, such as the Birthday field.
In the example of select dateadd (DD, 3, '2017-10-05 '), the result is 2008-10-08.

Datediff function: datediff (interval, date1, date2 [, firstdayofweek [, firstweekofyear]) returns a value, which indicates the interval between date1 and date2.

Interval indicates the time interval, which can be YY (year), mm (month), DD (day), QQ (quarter), ww (week ), HH (hours), n (minutes), S (seconds)

Date1 and date2 are two dates for calculation.

Firstdayofweek is optional. Specifies the constant of the first day of a week. If not specified, Sunday is the first day.

Firstweekofyear is optional. Specifies the constant of the first week of a year. If this parameter is not specified, the week that contains April 1 is the first week.

Example: Select datediff (DD, '2017-10-05 ', '2017-10-08') returns 3

 

To understand the usage of the above two functions, we can use them to obtain the employee list for a birthday in a certain period of time. For example, the table storing employee information is staffbasic, and the employee birthday field in the table is named Birthday, the begintime variable stores the start time of the query birthday obtained from the foreground, and the endtime variable stores the end time of the query birthday obtained from the foreground. The SQL statement is written as follows:

Select * From staffbasic where

(Dateadd (year, datediff (year, birthday, '"+ begintime +"'), birthday) between' "+ begintime +" 'and' "+ endtime + "'

Or

 Dateadd (year, datediff (year, birthday, '"+ begintime +"'), birthday) between' "+ begintime +" 'and' "+ endtime + "')

Analyze the meaning of this statement:

(Dateadd (year, datediff (year, birthday, '"+ begintime +"'), birthday) and

 Dateadd (year, datediff (year, birthday, '"+ endtime +"'), birthday)

The function is to change the Year of birth of all employees to the year of the date entered in the query. Because the foreground query inputs two dates, the two dates may have different years, for example, the front-end queries the birthday staff list from-12-20 to-1-20, so we need to consider the two years, then, use between' "+ begintime +" 'and' "+ endtime +" 'to filter matching records.