Comparison of SQL Server Date field values

Comparison of Date field values in SQL Server
There are a number of ways to compare Date fields in SQL Server, which are described in several common ways:
Use the Employees table in the Northwind library as the use Case table.
1.BetweenThe use of the ... and statement:
DescriptionBetween... and for specifying the test scope
See the following examples:
Execute SQL Statement "SELECTHireDateFromEmployees "displays the results as follows:
HireDate
1992-05-0100:00:00.000
1992-08-1400:00:00.000
1992-04-0100:00:00.000
1993-05-0300:00:00.000
1993-10-1700:00:00.000
1993-10-1700:00:00.000
1994-01-0200:00:00.000
1994-03-0500:00:00.000
1994-11-1500:00:00.000

From the result set above, search out hiredate in the "1993-10-17To1994-01-02", the SQL statement is as follows:
SELECTHireDate
FromEmployees
WHEREHireDateBetweenCast(‘1993-10-17‘ AsDatetime)andCast(‘1994-01-02‘AsDatetime)
After executing the statement, the result is as follows:
HireDate
1993-10-1700:00:00.000
1993-10-1700:00:00.000
1994-01-0200:00:00.000
Between...and and cast appear in the above SQL statement, where cast is a type conversion function: In this example, the string is converted to a date type value.
In where, use Between...and to "1993-10-17 "to" 1994-01-02 "records are searched. 2. The comparison size of two date values can be used with the < <= > >= operator, and the DATEDIFF function DATEDIFF function: DATEDIFF ( DatePart, StartDate, enddate) datepart The value can be 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. Look at the following example: A record of the value of the search HireDate after "1993-05-03", the SQL statement is as follows: Select Hiredatefrom employeeswhere datediff (day,cast (' 1993-05-03 ' as datetime), HireDate) >0 after executing the statement, the result is as follows: HIREDATE1993-10-17 00:00:00.0001993-10-17 00:00:00.0001994-01-02 00:00:00.0001994-03-05 00:00:00.0001994-11-15 00:00:00.000 so DateDiff (Day,cast (' 1993-05-03 ' as DateTime), HireDate) is to subtract the value of cast (' 1993-05-03 ' as DateTime) by the value of HireDate by "Day". Determine the order of the date by determining the positive or negative value of the subtraction

Comparison of SQL Server Date field values