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?

possible duplicate of [Get row where datetime column = today - SQL server noob](http://stackoverflow.com/questions/2583228/get-row-where-datetime-column-today-sql-server-noob) — bummi, Oct 16 '14 at 22:09

jpw · Accepted Answer · 2014-10-16T22:12:53.077

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

I like it a lot when you not only post the answer but also explain it with examples. Great! — Ronaldinho Learn Coding, Oct 16 '14 at 23:06

score 1 · Answer 2 · answered Oct 16 '14 at 22:13

1

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);

answered Oct 16 '14 at 22:13

user353gre3

2,747
4
24
27

score 0 · Answer 3 · edited Apr 16 '16 at 19:57

0

SELECT * FROM Customer WHERE visitTime > =  convert(date,getdate()) 
and visitTime <  dateadd(d,1,convert(date,getdate()))

edited Apr 16 '16 at 19:57

Paul Roub

36,322
27
84
93

answered Apr 16 '16 at 18:57

user3203114

21
3

score 0 · Answer 4 · edited Jan 10 '17 at 12:10

0

Check this out

select *
from Customers
where convert(date,createddate) = convert(date,getdate());

edited Jan 10 '17 at 12:10

iamdave

12,023
3
24
53

answered Jan 10 '17 at 11:50

100Lords

1

score 0 · Answer 5 · edited Apr 05 '18 at 12:19

0

Gets detail of to date only:
SELECT * FROM PRODUCTION WHERE CAST(CREATIONDATE AS DATE) = CAST(GETDATE() AS DATE)

edited Apr 05 '18 at 12:19

Ferdipux

5,116
1
19
33

answered Apr 05 '18 at 11:56

Rahul Singh

31
2

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

5 Answers5