Как работают условные операторы для дат в MS SQL Server и какой следует использовать?

Делаю выборки данных за 2020 год тремя разными способами:

Каждый раз я получаю разные результаты. Почему так происходит и какой способ следует использовать?

CreationDate имеет тип datetime. Под разными результатами я имею в виду количество записей получаемых при Count(*).

Автор решения: Aziz Umarov

Давайте разбирать

year(CreationDate) = '2020' - весь 2020 с [01-01-2020 00:00:00.000 до 12-31-2020 23:59:59.999] тут без проблем весь 2020
CreationDate > '12-31-2019' AND CreationDate < '01-01-2021' - с [12-31-2019 00:00:00.001 до 12-31-2020 23:59:59.999] - тут весь 2020 + лишний период 12-31-2019 00:00:00.001
CreationDate BETWEEN '01-01-2020' AND '12-31-2020' - с [01-01-2020 00:00:00.000 до 12-31-2020 00:00:00.000] - тут весь 2020 -и не хватающий почти день период 12-31-2019 00:00:00.001

Как видно из интервалов это не совсем 2020 год в некоторых случаях. Более подробно с примерами тут

Проблема во времени которые путают все карты. Для получения верного результата нужно обе части сравнения приводить к Date

year(CreationDate) = '2020' - тут нет проблем
Нужно Cast(CreationDate As Date) > '12-31-2019' AND Cast(CreationDate As Date) < '01-01-2021' - преобразовать явно к Date
Cast(CreationDate As Date) BETWEEN '01-01-2020' AND '12-31-2020' - преобразовать явно к Date

→ Ссылка