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

Question

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?

Solution 1

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

Solution 2

Assuming today is midnight last night to midnight tonight, you can use following condition

Select * from Customer where 
visitTime >=  DateAdd(d, Datediff(d,1, current_timestamp), 1)
and
visitTime < DateAdd(d, Datediff(d,0, current_timestamp), 1);

Comments

[Ronaldinho Learn Coding]

I like it a lot when you not only post the answer but also explain it with examples. Great!

[Igor Gorjanc]

nice one CAST(GETDATE() AS DATE)