Query where date = '2016/01/02' not working?

Question

SQL FIDDLE DEMO HERE

I have this structure of table:

CREATE TABLE Department
(
     [IdDepartment] int, 
     [Name] varchar(23), 
     [IdUser] int, 
     [CreateDate] datetime
);

INSERT INTO Department ([IdDepartment], [Name], [IdUser], [CreateDate])
VALUES
    (1, 'Sales', 3, '2016-01-15 17:00:00.000'),
    (2, 'Finance', null, '2016-01-13 18:00:00.000' ),
    (3, 'Accounting' , 5, '2016-03-21 22:00:00.000'),
    (4, 'IT' ,3, '2016-03-21 17:00:00.000'),
    (5, 'Secretary',null, '2016-03-21 17:00:00.000'),
    (6, 'Sport',3, '2016-02-20 16:00:00.000');

I want to run this query:

select Name 
from Department 
where CreateDate = '2016-03-21'

This returns 0 rows as a result.

I think it is because the date in the table structure is a datetime but I try to do this and neither works for me:

select Name 
from Department 
where CreateDate like '%2016-03-21%'

The result should be this:

Name
-----
Accounting
IT
Secretary

How can I get this result?

I hope I explained clearly, thanks

Answer 1

You are comparing a DATETIME value against a pure DATE. You must be aware, that a DATETIME includes a time

2016-03-21 11:00:00 is NOT equal to 2016-03-21

You might compare like this

CAST(YourDate AS DATE)=CAST('2016-03-21' AS DATE)

Hint: As one tiny exception! - CAST(someCol AS DATE) actually is sargable, but it's worth to mention, that it still is not the best idea to do: dba.stackexchange.com/a/34052/70663

or you could try a BETWEEN

YourDate BETWEEN {d'2016-03-21'} AND {ts'2016-03-21 23:59:59'}

or - which is the most prefered in most cases

YourDate >= {d'2016-03-21'} AND YourDate < {d'2016-03-22'}

Avoid manipulations to the column value due to sargability

Better avoid date literals... If you have to, you might read this