Select last completed hour.

spudmcc
spudmcc used Ask the Experts™
on
Hi Experts

I need to be able to select only the records from the last completed hour of the current day.  So if it 8:49 am currently and the report runs, it would only select the records from 7 am until 7:59 am.  

I've tried a number of things but I am just not getting it right.  Your help is greatly appreciated as always.

A
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Hi,

Are you trying to filter only for time part or including date part?

Here is what you can try for time for filtering:

Add a filter condition like {database field} >=time(hour({your time field})-1,00,00) and {database field} <= time(hour({your time field})-1, 59,00)?

Replace {Database field} with time field from database and {your time field} with the time extracted for filtering purpose.

Let us know how it goes.

Author

Commented:
It gives me an error.  Date-Time field required and highlights the time(hour area.  I've replaced the database field with the date/time field and the (your time field) with the time extract.
Commented:
Ok, so, you are trying to filter out with a date time field.

Here is what you need to change:

{database field} >= datetime(year({datetime field}),month({datetime field}), day({datetime field}), hour({datetime field})-1,00,00) and {database field} <= datetime(year({datetime field}),month({datetime field}), day({datetime field}), hour({datetime field})-1,59,00)

Hope that helps.
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

Author

Commented:
Thank you so much.  This solves a problem that has been a "thorn in my side" for some time.  

Andy

Commented:
Glad I could help :)
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Glad that works but you could try

{database field} >=  DateAdd('h', -1, CUrrentDateTime) AND {database field} <= CurrentDateTime

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial