SQL Server: how to select records with specific date from datetime column

Question

I am pretty new to SQL and hope someone here can help me with this:

I have a table with one column dateX formatted as datetime and containing standard dates. How can I select all records from this table where this dateX equals a certain date, e.g. May 9, 2014 ?

I tried the following but this returns nothing even if I have several records with this date.

SELECT     *
FROM         dbo.LogRequests
WHERE     (CONVERT(VARCHAR(10), dateX, 101) = '09/05/14')

Edit: In the database the above example looks as follows, using SQL 2012: 2014-05-09 00:00:00.000

Many thanks for any help with this, Mike.

Answer 1

The easiest way is to convert to a date:

SELECT *
FROM dbo.LogRequests
WHERE cast(dateX as date) = '2014-05-09';

Often, such expressions preclude the use of an index. However, according to various sources on the web, the above is sargable (meaning it will use an index), such as this and this.

I would be inclined to use the following, just out of habit:

SELECT *
FROM dbo.LogRequests
WHERE dateX >= '2014-05-09' and dateX < '2014-05-10';

Answer 2

For Perfect DateTime Match in SQL Server

SELECT ID FROM [Table Name] WHERE (DateLog between '2017-02-16 **00:00:00.000**' and '2017-12-16 **23:59:00.999**') ORDER BY DateLog DESC