Solved

SQL 2005 need help with Time & Date

Posted on 2012-04-13
7
463 Views
Last Modified: 2012-06-22
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
Comment
Question by:Alice7
  • 4
  • 2
7 Comments
 
LVL 42

Assisted Solution

by:dqmq
dqmq earned 250 total points
ID: 37843070
where logdate > dateadd(h,-1,getdate()) and logdate <= getdate()
0
 

Author Comment

by:Alice7
ID: 37843234
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
 

Author Comment

by:Alice7
ID: 37843281
Dqmq I made the following change and it seems to be working now.

where logdate > dateadd(HH,-1,getdate()) and logdate <= getdate()
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 23

Expert Comment

by:wdosanjos
ID: 37843361
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
 

Author Comment

by:Alice7
ID: 37843732
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
 
LVL 23

Accepted Solution

by:
wdosanjos earned 250 total points
ID: 37843892
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
 

Author Comment

by:Alice7
ID: 37844000
Thanks wdosanjos that worked perfect!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

775 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question