CODEWITHSUNDEEP
sql-serverdatetime
Isawpalmetto
Isawpalmetto

Reputation: 795

How to compare a datetime type in SQL Server

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.

Upvotes: 3

Views: 670

Answers (5)

Waleed A.K.
Waleed A.K.

Reputation: 1656

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

Upvotes: 1

Mark Ransom
Mark Ransom

Reputation: 308101

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'

Upvotes: 7

MikeTWebb
MikeTWebb

Reputation: 9279

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

Upvotes: 0

froadie
froadie

Reputation: 82993

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'

Upvotes: 0

JonH
JonH

Reputation: 33141

Strip the time if that is irrelevant

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

Upvotes: 0

Related Questions