• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 479
  • Last Modified:

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.
0
Alice7
Asked:
Alice7
  • 4
  • 2
2 Solutions
 
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
 
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
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
 
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.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now