asked on

MS Access SQL select by hour of day

Hi there I am trying to write a reusable SQL statement that I can run daily where I can select all the records in my table before 13:00:00 each day.
My date format is stored as a date time stamp I have looked at using the date part function but I can't get it working.

SELECT datePart("hh",CustTable.[Log Time]) AS Hour
FROM faculty2013
WHERE ((CustTable.[Booklet Received])=1 and hour <= '13:00:00');

ButlerTechnology

You can't use an alias in a where clause. You can rewrite the function in the where clause

SELECT datePart("hh",CustTable.[Log Time]) AS Hour
FROM faculty2013
WHERE ((CustTable.[Booklet Received])=1 and datePart("hh",CustTable.[Log Time])<= '13:00:00');

Open in new window

The alias can be used in the Order By Clause.

Tom

SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jay_eire

ASKER

Thanks tom does the time part look correct in my SQL statement will that work on a date time stamp column?

ButlerTechnology

After looking it over, you should make a few minor adjustments. The Datepart will return an integer, so you can change the '13:00:00' to just be 13.

Tom

jay_eire

ASKER

@tom I tried that statement but I'm getting back an error or Invalid procedure call I checked the first part of the query without the where clause and hour is being selected as #error

jay_eire

ASKER

@jimhorn thanks for reply I checked that statement but it is returning all the rows in my table from all dates.
I'm trying to get the query to select the rows based on the current date only and before 13:00:00.

ASKER CERTIFIED SOLUTION

ButlerTechnology

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jay_eire

ASKER

Thanks so much both are you ok for me to split the points?

ButlerTechnology

I am good either way -- Jim did the heavy lifting.

Tom

jay_eire

ASKER

Thanks both for the Swift Response, I split points. Hope this is OK, if not let me know.

J

Jim Horn

Good enough. Thanks for the split, good luck with your project. -Jim