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

Strip the time if that is irrelevant

Dateadd(d, datediff(d,0,getdate()),0)

Answer 2

When I try running something similar in my version of SQL Server (2005), it works as expected.

Try taking out the PM and using 24 hour format instead:

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

Answer 3

That looks like it should work. You might try SELECT * FROM Res Where cast(dateSubmit as nvarchar) ='6/17/2010 5:01:26PM'

Answer 4

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 5

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