Mssql date and time data cannot be queried

Now we need to query all records on February 21:

The code is as follows:	Copy code
SELECT * FROM [A] WHERE [B] = '2017-06-21'

The preceding syntax does not query any records. Some netizens may think of using:

The code is as follows:	Copy code
SELECT * FROM [A] WHERE [B] BETWEEN '2017-06-21 00:00:00 'AND '2017-06-21 23:59:59'

In this way, you can query the data records of the current day by specifying the date range. However, if [B] has a storage period of 23:59:59. xxx, this record cannot be queried.

We can use the DATEADD function to add a day to the midnight time value of the current date, that is, get the midnight time value of the next day, and then subtract 2 seconds, that is, you can obtain the maximum time value of the current system date.

The code is as follows:	Copy code
DECLARE @ Date DATETIME = CURRENT_TIMESTAMP -- Current system Date and time DECLARE @ midnight DATETIME SET @ midnight = CAST (@ Date as date) as datetime) Select dateadd (MS,-2, DATEADD (day, 1, @ midnight) -- 23:59:59. 997

The following is the Insus. NET method. This method can only be applied on SQL Server 2008 or later versions, because only the DATE and TIME data types are available in this version.

The code is as follows:	Copy code
Version 1: Select cast (CURRENT_TIMESTAMP as date) as char (10) + ''+ '00: 00: 00.000 ') as datetime) Version 2: Select cast (CURRENT_TIMESTAMP as date) as datetime)