I have a record with an [Effective Date] of '2017-12-31T13:29:50.000'. This is a datetime data type within SQL Server 2019, so it includes a date and a time.
I want to do an equals operator in the WHERE clause with a value that doesn't include the time. However, when I do this in this case it doesn't return the record:
WHERE [Effective Date] = '12/31/2017'
The record does return when I do a greater than and a less than:
WHERE [Effective Date] > '12/31/2017' AND [Effective Date] < '1/1/2018'
How can I be able to return that record when applying an equals operator (rather than with two operators) without including a time value?
In SQL Server, you can convert to a date to remove the time component:
WHERE convert(date, [Effective Date]) = '2017-12-31'
Happily, this is also index friendly (most function calls prevent the use of indexes, but this is an exception).
Note that this uses the ISO standard for date formats. The SQL standard is '20171231', but as a human I prefer the hyphen and they almost always work (there is one internationalization setting that causes a problem).