How to compare datetime type in SQL Server

Question

So, I have a column in my SQL Server database which is a datetime. I want to make this simple select statement:

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

dateSubmit is a datetime data type, and in my database there is a line where dateSubmit is on 6/17/2010 5:01:26 PM

However, when I execute this statement, it returns null.

Answer 1

I think SQL Server stores more precision digits than it shows.

Try the following:

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: second, and you will never get the exact section, so you will get zero. Try using

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

For more information see link

"" - , "DatePart" . , .

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

Answer 3

Divide time if it doesn't matter

Dateadd (d, dateiff (d, 0, getdate ()), 0)

Answer 4

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

Try to take PM and use the 24-hour format:

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

Answer 5

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