Alice7
asked on
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Dqmq I made the following change and it seems to be working now.
where logdate > dateadd(HH,-1,getdate()) and logdate <= getdate()
where logdate > dateadd(HH,-1,getdate()) and logdate <= getdate()
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()
where logdate between dateadd(hour, datepart(hour, getdate()), cast(cast(getdate() as date) as datetime)) and getdate()
ASKER
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.
Type date is not a defined system type.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks wdosanjos that worked perfect!
ASKER
Msg 155, Level 15, State 1, Line 2
'h' is not a recognized dateadd option.