SQL 2005 need help with Time & Date

Via MS SQL Server 2005

Hello everyone, I have a table that has a logdate column in this format 2012-04-13 07:42:59.387.
How would I select everything within only the current hour?

Reason is I am going to have a page that will refresh itself every minute and use this data to show the user what is going on in the current hour.
Alice7Asked:
Who is Participating?
 
wdosanjosCommented:
That means you are on SQL 2005.  date was introduced with SQL 2008.

Please try: (actually a simpler solution)
where logdate between dateadd(hour, datediff(hour,0, getDate()), 0) and getdate()

Open in new window

0
 
dqmqCommented:
where logdate > dateadd(h,-1,getdate()) and logdate <= getdate()
0
 
Alice7Author Commented:
Hello Dqmq, thank you for the help. I tried the suggested and got the following error.

Msg 155, Level 15, State 1, Line 2
'h' is not a recognized dateadd option.
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
Alice7Author Commented:
Dqmq I made the following change and it seems to be working now.

where logdate > dateadd(HH,-1,getdate()) and logdate <= getdate()
0
 
wdosanjosCommented:
The query returns all entries within the last one hour.  If you need the records on the current hour only the query would be:


where logdate between dateadd(hour, datepart(hour, getdate()), cast(cast(getdate() as date) as datetime)) and getdate()
0
 
Alice7Author Commented:
Hello wsosanjos, I think all records on the current hour is what I am looking for. I tried your suggestion and got the following error.

Type date is not a defined system type.
0
 
Alice7Author Commented:
Thanks wdosanjos that worked perfect!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.