Ronaldinho Learn Coding Ronaldinho Learn Coding - 7 months ago 30
SQL Question

SQL display | select data from today date | current day only

I have this query, it supposes to display data from current date (today) only

SELECT * FROM Customer WHERE visitTime <= GETDATE() AND visitTime > GETDATE() - 1


where visitTime is datetime type

But it doesn't work well because I think problem is at
AND visitTime > GETDATE() - 1
but I don't know how to fix this, anybody has any suggestions?

jpw jpw
Answer

Look at this example:

declare @visitTime datetime  ='2014-10-16 23:59:59.000'
select GETDATE() GETDATE, @visitTime visitTime, GETDATE() - 1 [GETDATE-1]

GETDATE                 visitTime               GETDATE-1
2014-10-17 00:02:18.980 2014-10-16 23:59:59.000 2014-10-16 00:02:18.980

You'll see that the visittime date clearly falls in the range you specified as the lower bound (the -1) subtracts a whole day and not just the time part.

You could use this instead:

-- using GETDATE() for the upper bound misses visitTime that are 
-- on the current day, but at a later time than now.
WHERE visitTime < DateAdd(Day, DateDiff(Day, 0, GetDate())+1, 0)
AND   visitTime >= DateAdd(Day, DateDiff(Day, 0, GetDate()), 0)

or if you're on SQL Server 2008+ that has adatedata type, this:

WHERE CAST(visitTime AS DATE) = CAST(GETDATE() AS DATE)

Note thatGETDATE()is T-SQL specific, the ANSI equivalent isCURRENT_TIMESTAMP

Comments