timestamp date today

Question

Hi guys could you please help me with this. I know it is silly but can't find unswer anywhere:

I have a query that shows events from the today time_stamp but I want to include today as well. How do I add this to this query?

 ...
WHERE events.event_date > current_timestamp
ORDER BY convert (event_date, date) ASC

As it is formed it doesn't show the event of the day. Hope I made it clear. Please help me many thanks. F

Answer 1

If i understand correctly current_timestamp is a datetime containing hh:mm:ss.

To get the date only use

DATEADD(dd,0, DATEDIFF(dd,0current_timestamp))

Which should get youjust the Day Month Year value.

Then you can change your code to

WHERE events.event_date >= DATEADD(dd,0, DATEDIFF(dd,0current_timestamp))
ORDER BY convert (event_date, date) ASC

Answer 2

Try this

declare @t table(eventdt datetime)
insert into @t 
    select '12/12/2009' union all 
    select '12/15/2009'union all 
    select '11/27/2009'union all -- today's date
    select '11/26/2009' union all
    select '10/26/2009'
select * from @t where eventdt > =  CONVERT(varchar(10),current_timestamp,101)

Output:

eventdt
2009-12-12 00:00:00.000
2009-12-15 00:00:00.000
2009-11-27 00:00:00.000

Answer 3

for anybody who might have the same problem I sorlved it as follow:

 WHERE events.event_date > current_timestamp 
 OR TIMESTAMPDIFF(DAY, events.event_date, CURRENT_TIMESTAMP) = 0 
 ORDER BY convert (event_date, date) ASC ";