CODEWITHSUNDEEP
sqldatetime
Arcadian
Arcadian

Reputation: 4350

Strange SQL results when date condition in WHERE

I have a table TABLE1 with a creationdate field (datetime) with the following example records

ID 1 12/12/2015 1:00:45    
ID 2 12/13/2015 00:00:00

if I execute the following query in studio manager it returns both records

the same query returns the correct records in a stored procedure

SELECT *
FROM TABLE1
WHERE CreationDate >= '12/12/2015' AND CreationDate <= '12/12/2015 23:59:59'

the query above is returning both records in studio manager. When it should only returning ID 1

I ran the same query by placing it in a stored proc and it returns the correct results.

Upvotes: 0

Views: 40

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270653

This is too long for a comment and it might not fix your specific problem.

But, you should really use ISO standard date formats -- either YYYYMMDD or YYYY-MM-DD. In addition, there is no need to have seconds in the logic. How about just doing:

SELECT *
FROM TABLE1
WHERE CreationDate >= '2015-12-12' AND CreationDate < '2015-12-13'

Easier to type and it will even get times a few milliseconds before midnight.

Two common possible problems are (1) time zone issues (but this is ruled out with datetime) or (2) rounding issues. Neither seems likely in this case.

Upvotes: 1

Related Questions