Parse Time out of A DateTime Field in Access 2016

Question

I need to check the records of an Access Database that has a SigninTime field of data type DATETIME for any sign in that occurred between 6PM and 8PM.

I've tried:

SELECT TestingStatistics.SigninTime
FROM TestingStatistics
WHERE datepart(h,TestingStatistics.SigninTime) >=18;

Which asks me to define H

And

SELECT TestingStatistics.SigninTime
FROM TestingStatistics
WHERE TestingStatistics.SigninTime >=18;

Which just returns everything.

How do you search a DATETIME Field using Time instead of a date? Furthermore what query should I run?

Answer 1

Try making it a string:

datepart("h", TestingStatistics.SigninTime) >= 18;

Answer 2

You can extract JUST the date part of a date/time column with:

DateValue( some date time)

And you can extract JUST the time part of a date/time column with:

TimeValue( some date time)

So, this query would work.

Select SignInTime from TestingStatistics
Where TimeValue(SignInTime) between #6 pm# and #8 pm#

Answer 3

You can try something like this:

Where TestingStatistics.SigninTime> Convert(datetime,(Replace(Convert(varchar,GETDATE(),104),'.','-')+ ' 18:00:00.000'),104) and TestingStatistics.SigninTime <= Convert(datetime,(Replace(Convert(varchar,GETDATE(),104),'.','-')+ ' 20:00:00.000'),104))

This should show you all the record of today between 18PM-20PM, but You can replace Getdate() for any date that you need.

Hope this help!