Solved

SQL 2005 need help with Time & Date

Posted on 2012-04-13
7
462 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Using CTE to insert records into a table 2 28
Max Consumption Rate (MCR) 3 33
SQL Server Question 5 25
Generate Weekly Schedule 15 16
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

910 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now