Comparison of date fields (datetime) in SQL Server

In SQL Server, there are multiple methods to compare date fields. Several common methods are introduced:
Use the Employees table in the northwind database as the use case table.
1. Use the between... and statement:
Description: between... and is used to specify the test scope.
See the following example:
Execute the SQL statement "select hiredate from employees" and the result is as follows:
Hiredate
00:00:00. 000
1992-08-14 00:00:00. 000
00:00:00. 000
00:00:00. 000
1993-10-17 00:00:00. 000
1993-10-17 00:00:00. 000
00:00:00. 000
1994-03-05 00:00:00. 000
1994-11-15 00:00:00. 000

Search for records with hiredate from "1993-10-17" to "1994-01-02" in the preceding result set. The SQL statement is as follows:
Select hiredate
From employees
Where hiredate between cast ('2017-10-17 'As datetime) and cast ('2017-01-02' As datetime)
The result is as follows:
Hiredate
1993-10-17 00:00:00. 000
1993-10-17 00:00:00. 000
00:00:00. 000
Between... and cast appear in the preceding SQL statement. Cast is a type conversion function. In this example, the string is converted to a date value.
Use between... and in the WHERE clause to search records from "1993-10-17" to "1994-01-02.

2. You can use the <<=>= operator and the datediff function to compare two date values.
Datediff function: datediff (datepart, startdate, enddate)
Datepart can be set to year, quarter, month, dayofyear, day, week, hour, minute, second, millisecond.
Startdate is subtracted from enddate. If startdate is later than enddate, a negative value is returned.
See the following example:
Search for records whose hiredate value is after "". The SQL statement is as follows:
Select hiredate
From employees
Where datediff (day, cast ('2017-05-03 'As datetime), hiredate)> 0
The result is as follows:
Hiredate
1993-10-17 00:00:00. 000
1993-10-17 00:00:00. 000
00:00:00. 000
1994-03-05 00:00:00. 000
1994-11-15 00:00:00. 000
Therefore, datediff (day, cast ('1970-05-03 'As datetime), hiredate) is to divide the value of hiredate by "day" minus cast ('1970-05-03' As datetime)
To determine the order of dates by determining the plus or minus values.