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

Question

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

score 5 · Answer 1 · edited May 23 '17 at 12:15

5

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

edited May 23 '17 at 12:15

Community

1
1

answered Apr 04 '16 at 10:02

Shnugo

66,100
9
53
114

Thanks for your answer :-) – Esraa_92 Apr 04 '16 at 10:05
1

@Shnugo: this man doesn't give fix, he has solution :), now in future you(@Esraa_92) will always alert – Vinay Sinha Apr 04 '16 at 13:23

score 2 · Accepted Answer · edited Apr 04 '16 at 22:35

2

Use CAST method to compare date only not time

SELECT * FROM Department 
WHERE CAST(CreateDate AS DATE) ='2016-03-21'

edited Apr 04 '16 at 22:35

Shyam Bhimani

1,310
1
22
37

answered Apr 04 '16 at 09:45

Jaydip Jadhav

12,179
6
24
40

1

@Esraa_92 Allthough - as one tiny exception! - `CAST(someCol AS DATE)` actually **is** sargable, it's worth to mention, that it still is not the best idea to do: http://dba.stackexchange.com/a/34052/70663 – Shnugo Apr 04 '16 at 10:36

score 0 · Answer 3 · answered Apr 04 '16 at 13:11

Fully agree with @Shnugo

DECLARE @Department TABLE (
    IdDepartment INT,
    Name VARCHAR(23),
    IdUser INT,
    CreateDate DATETIME
);

INSERT INTO @Department
VALUES
    (1, 'Sales', 3, '2016-01-15 17:00:00'),
    (2, 'Finance', NULL, '2016-01-13 18:00:00'),
    (3, 'Accounting', 5, '2016-03-21 22:00:00'),
    (4, 'IT', 3, '2016-03-21 17:00:00'),
    (5, 'Secretary', NULL, '2016-03-21 17:00:00'),
    (6, 'Sport', 3, '2016-02-20 16:00:00')

DECLARE @Date DATETIME = '20160321' -- ISO format

SELECT Name
FROM @Department
WHERE CreateDate >= @Date
    AND CreateDate < DATEADD(DAY, 1, @Date)

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

3 Answers3