CODEWITHSUNDEEP
sqlsql-servert-sql
AndrewMo
AndrewMo

Reputation: 149

Using a date value in where clause on a datetime data type

Is there anything I should be cautious of when using a date in the where statement if the field I am filtering on is a datetime? Does WHERE Date = '20190604' actually get interpreted as '2019-06-04 00:00:00'

Ran a few queries to see if any values drop based on the different ways the date can be formatted. Did not notice any difference.

SELECT DateField
FROM CalendarTbl
WHERE DateField = '20190604'

Upvotes: 0

Views: 5105

Answers (3)

Thom A
Thom A

Reputation: 96016

If you pass a string like '20190604' to a datetime then yes, it'll be interpreted as the date and time 2019-06-04T00:00:00.000. This means that if you have any values that aren't on the stroke of midnight then the row won't be returned (even if that value is 2019-06-04T00:00:00.003).

If you do have values other than at midnight and you want to return all rows for a specific date you can use CONVERT/CAST to change the data type of the column to date; which is still SARGable:

SELECT DateField
FROM CalendarTbl
WHERE CONVERT(date,DateField) = '20190604';

Upvotes: 4

Zohar Peled
Zohar Peled

Reputation: 82534

Yes, the string literal '20190604' will always be interpreted by SQL Server as yyyymmdd - 2019-06-04. If you only supply the Date, time is assumed as midnight - so 20190604 is equivalent to 2019-06-04T00:00:00.

Please note that if you use 2019-06-04 with the DateTime data type it is still culture-dependent.

If you want all the records for that specific date, you need to add another condition:

SELECT DateField
FROM CalendarTbl
WHERE DateField => '20190604'
AND DateField < '20190605'

Upvotes: 1

George Menoutis
George Menoutis

Reputation: 7260

Yes it's fine. Your string gets implicitly cast to the needed datetime. I'd wager it's the fastest way for human-inputting dates.

Check the colored table in the the official documentation for a list of implicit and explicit casts.

Upvotes: 1

Related Questions