CODEWITHSUNDEEP
sqlsql-server
fraXis
fraXis

Reputation: 3221

SQL getdate() in Count query not working correctly

I am trying to execute this query against SQL Server 2008:

SELECT Count(*) FROM tblSharesSentRequests WHERE [SentDate] = getdate()

This returns 0 rows, even though I have 1 row with a SentDate set to "2012-7-13"



This SQL statement works fine:

SELECT Count(*) FROM tblSharesSentRequests WHERE [SentDate] = '2012-7-13'

it returns 1 row.



Why doesn't it work when I use getdate()?

Upvotes: 0

Views: 11512

Answers (5)

JonH
JonH

Reputation: 33143

I hope SentDate is a datetime type variable, is it?

If so try to strip the time from date time:

SELECT DATEADD(d, DATEDIFF(d, 0, GetDate()), 0)

So your query becomes:

SELECT Count(*) FROM tblSharesSentRequests WHERE [SentDate] = DATEADD(d, DATEDIFF(d, 0, getdate()), 0)

If you are using 2008 you can do as the comment states: CAST(GetDate() as date).

A simpler way express it is:

SELECT Count(*)
FROM tblSharesSentRequests
WHERE [SentDate] = cast(getdate() as date)

The type ended up being just a Date and not a datetime. The answer to this was to use the following:

WHERE SentDate = CONVERT(Date, GetDate())

Upvotes: 2

Gulli Meel
Gulli Meel

Reputation: 891

getdate() gives not just the date it provides the hour minute etc.Thus you got the 0 values as you did not have exact match. Best way to do is SELECT Count(*) FROM tblSharesSentRequests WHERE [SentDate] = cast(getdate() as date)

Upvotes: -1

Rick Gittins
Rick Gittins

Reputation: 1138

The SQL function GETDATE() returns the date and time. You are comparing it to a field that I'm guessing has only the date. You will need to do something like this:

SELECT Count(*) 
  FROM tblSharesSentRequests 
 WHERE [SentDate] = CAST(GETDATE() AS date)

Upvotes: -1

anon
anon

Reputation:

If SQL Server 2008:

WHERE SentDate >= CONVERT(DATE, GETDATE())
  AND SentDate <  DATEADD(DAY, 1, CONVERT(DATE, GETDATE());

(Or if it is a DATE column)

WHERE SentDate = CONVERT(DATE, GETDATE());

If < SQL Server 2008:

WHERE SentDate >= DATEDIFF(DAY, 0, GETDATE())
  AND SentDate <  DATEDIFF(DAY, -1, GETDATE());

Upvotes: 4

Chandu
Chandu

Reputation: 82903

Try this:

SELECT Count(*) 
  FROM tblSharesSentRequests 
 WHERE [SentDate] = CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)

Upvotes: 0

Related Questions