CODEWITHSUNDEEP
sql-servert-sql
Nasko
Nasko

Reputation: 77

SQL Server query can`t show filter results correctly

I have sql server query like that:

SELECT * FROM mytable 
WHERE company_name = 'Shell'
  and datetime >= '2017-03-06' 
  and datetime <= '2017-03-06' 
ORDER BY datetime DESC

Now that code show me all Shell rows even outside of the searched date period.

I want Shell rows only in 2017-03-06.

Where is my mistake?

This code usually work well in MySQL server.

Upvotes: 0

Views: 77

Answers (5)

etsa
etsa

Reputation: 5060

I think it's caused by your server setting for date (it's not using 03 as month, but as day). Use always

CONVERT when work with a date (for insert, in where, etc.). Eg. CONVERT(datetime, '2017-03-06',121) if date is in the format 'yyyy-mm-dd'.

CREATE TABLE mytable (ID INT, COMPANY_NAME VARCHAR(10), DATETIME DATETIME)
SELECT * FROM mytable

INSERT INTO mytable VALUES (1,'Shell', '2017-03-06')
INSERT INTO mytable VALUES (2,'Shell', '2017-03-07')
INSERT INTO mytable VALUES (3,'Shell', '2017-03-05')
INSERT INTO mytable VALUES (4,'Shell', '2017-03-06')


INSERT INTO mytable VALUES (5,'Nut', CONVERT(datetime, '2017-03-06',121))
INSERT INTO mytable VALUES (6,'Nut', CONVERT(datetime, '2017-03-07',121))
INSERT INTO mytable VALUES (7,'Nut', CONVERT(datetime, '2017-03-05',121))
INSERT INTO mytable VALUES (8,'Nut', CONVERT(datetime, '2017-03-06',121))


---Results can be uncorrect
SELECT ID, COMPANY_NAME, DATETIME, CONVERT(varchar,DATETIME,121) AS DATE_STR
FROM mytable
WHERE company_name = 'Shell'
    AND DATETIME >= '2017-03-06'
    AND DATETIME <= '2017-03-06'
ORDER BY DATETIME DESC

---Results correct
SELECT ID, COMPANY_NAME, DATETIME, CONVERT(varchar,DATETIME,121) AS DATE_STR
FROM mytable
WHERE company_name = 'Nut'
    AND DATETIME >= CONVERT(datetime, '2017-03-06',121)
    AND DATETIME <= CONVERT(datetime, '2017-03-06',121)
ORDER BY DATETIME DESC

Upvotes: 1

Dogan
Dogan

Reputation: 106

The most important thing about sql server. "Always consider twice what you write in where criteria" Other answers can solve your problem but the main problem is your algoritm or your logic when coding sql. What you need ? The answer of this question is "specific day" so specify just date. Do not use <>. I see you used these logic for datetime format. So convert to date and then compare. If you do not need hour, minute or something waste them. This logic is always makes simple your problems. best regards.

Upvotes: 0

MK_
MK_

Reputation: 1169

You can do this:

SELECT * 
FROM mytable 
WHERE company_name = 'Shell' 
  and datetime >= '2017-03-06' 
  and datetime < '2017-03-07' 
ORDER BY datetime DESC

It will pick up all the rows whose datetime is at any point of '2017-03-06'.

Or cast the datetime to date data type as Anil Kumar suggested and then use the datetime = '2017-03-06' clause.

Upvotes: 0

Anil
Anil

Reputation: 3752

CAST(datetime AS DATE) and compare with date only

SELECT * FROM mytable WHERE company_name = 'Shell' and CAST(datetime  AS DATE)= '2017-03-06' ORDER BY datetime DESC

Upvotes: 1

鄭有維
鄭有維

Reputation: 265

SELECT * FROM mytable WHERE company_name = 'Shell' and datetime like '2017-03-06%' ORDER BY datetime DESC

Upvotes: 0

Related Questions