How to compare a datetime type in SQL Server

Question

So I have a column in my DB on SQL Server that is a datetime. I want to do this simple select statement:

SELECT *
FROM Res
Where dateSubmit='6/17/2010 5:01:26 PM'

dateSubmit is a datetime data type and my database has a row where dateSubmit is 6/17/2010 5:01:26 PM

However, when I execute this statement it returns null.

Answer 1

I think SQL Server keeps more digits of precision than it is displaying.

Try this:

SELECT * 
FROM Res 
Where dateSubmit between '6/17/2010 5:01:26 PM' and '6/17/2010 5:01:27 PM'

Answer 2

The problem is the hour: minute: sec part and you will never get exact sec that’s why you’ll get null. Try to use

SELECT * 
FROM Res
Where DATEDIFF ( day , dateSubmit , '6/17/2010 5:01:26 PM' )=0

For more information look to this link

The “day” could be replace by anything else as “DatePart” see the link e.g.

SELECT * 
FROM Res
Where DATEDIFF ( minute, dateSubmit , '6/17/2010 5:01:26 PM' )=0