Extract Hour and Minute from date field for where clause

I want to create a calculated field to extract the hour and minute field from a date field and then designate whether or not it is Day or Night shift.

For example:

Date = 3/30/2015 11:56 AM

The HH:MM would be 11:56.

If the MM/DD/YYYY HH:MM is between 3/30/2015 7:00 and 3/30/2015 19:15 it is Day Shift.

Date = 3/23/2015 11:56 PM

The HH:MM would be 21:56.

If the MM/DD/YYYY HH:MM is between 3/23/2015 19:00 and 3/24/2015 07:15 it is Night Shift.

What is the syntax to make this comparison?

Thanks

Glen
GPSPOWAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Walter RitzelSenior Software EngineerCommented:
I dont think you need to extract just hour and minute for that comparison.
You can use your fields exactly as you have used in your question, but in a formal logical expression.
PatHartmanCommented:
IIf(Format(YourTimeStamp, "hh:nn") >= #7:00# and Format(YourTimeStamp, "hh:nn")  <= #19:15#, "Day Shift", "Night Shift")
Rey Obrero (Capricorn1)Commented:
Test this, change dateField with actual name of the field

IIF(timeserial(hour([datefield]),minute([datefield]),0) >=#7:00# And timeserial(hour([datefield]),minute([datefield]),0) <=#19:15#, "Day Shift", "Night Shift")
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

GPSPOWAuthor Commented:
I have tried both suggestions in Access and neither were accepted as viable formulas.

What would be the comparable code in SQL 2008?

Thanks

Glen
PatHartmanCommented:
You could also try:

IIf(TimePart(YourTimeStamp) >= #7:00# and TimePart(YourTimeStamp)  <= #19:15#, "Day Shift", "Night Shift")

Please post the code you used that did not work.
Is "YourTimeStamp" field defined as date/time?
GPSPOWAuthor Commented:
here is what I just tried, substituting my FieldName for "YourTimeStamp"

IIf(TimePart( [dbo_vw_Restraint_Log_Data]![OrderDateTime] ) >= #7:00# and TimePart( [dbo_vw_Restraint_Log_Data]![OrderDateTime] )  <= #19:15#, "Day Shift", "Night Shift")


It did not work.

The field [dbo_vw_Restraint_Log_Data]![OrderDateTime]  is a Date/Time field.

glen
Gustav BrockCIOCommented:
It should read:

IIf(TimeValue([dbo_vw_Restraint_Log_Data]![OrderDateTime]) Between #7:00# And #19:15#, "Day Shift", "Night Shift")

/gustav
GPSPOWAuthor Commented:
I created the following SQL statement that is working now.

case when [OrderDateTime] >=DATEADD(minute,420,DATEADD(dd,datediff(dd,0,[OrderDateTime]),0))      and
      [OrderDateTime] <=DATEADD(minute,1155,DATEADD(dd,datediff(dd,0,[OrderDateTime]),0))      then 'DayShift' else
     
      case when [OrderDateTime] >= DATEADD(minute,1140,DATEADD(dd,datediff(dd,0,[OrderDateTime]),0))      and [OrderDateTime]
      <= DATEADD(minute,1875,DATEADD(dd,datediff(dd,0,[OrderDateTime]),0)) then 'Night Shift' else 'None' end
      end as Shift

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PatHartmanCommented:
Sorry, I meant TimeValue() rather than TimePart()  but the TimeValue() function relies on your system settings and so will require that you include AM or PM in your literal values if you are not using a 24 hour clock.

None of the Access functions will work in SQL so that is why you couldn't get any of them to work.  You didn't mention that your BE was SQL Server and you needed a T-SQL Solution.
GPSPOWAuthor Commented:
Pat,

My original question concerned writing the code for MS-Access.

I was trying all the solutions in MS-Access.  When they would not work I decided to try it in SQL instead.

Thanks

Glen
Rey Obrero (Capricorn1)Commented:
@GPSPOW,
you should have mentioned in your original post that you are accessing SQL tables.
PatHartmanCommented:
When you try our suggestions and they don't work, the most helpful thing is to paste the code/query you actually tried along with the exact error message or a description of the incorrect result.  When we create examples, they are almost always air code and so have not been syntax checked.  Once you substitute your own variable names and put the code in context, the syntax errors show up and we can help fix them.
Gustav BrockCIOCommented:
Mine was not air code and it works.

/gustav
jyk_ausCommented:
I have not tested it but the best way to compare times is to convert times to a numbers (between 0 and 1) where 07:00 is 0.291667 and 19:15 is 0.802083. Use the Int() function to get rid of full days.

IIf ( [OrderDateTime] - Int( [OrderDateTime] ) >= 0.291667 And [OrderDateTime] - Int( [OrderDateTime] ) <=0.802083,"Day Shift", Night Shift)
Gustav BrockCIOCommented:
It is never "the best way" to use magic numbers.

/gustav
GPSPOWAuthor Commented:
Pat,

First of all I tried the suggested code  and substituted my field names where needed and received no error.  In Access when an expression is in error, the code just disappears or the code that was there before is not overwritten.   The functions were not the problem.

Second, I did post the code I entered.  Before I received a response back I decided to try MS-SQL language instead of the MS-Access version and got the result I needed.  I just linked the SQL query to the Access database.

Glen
GPSPOWAuthor Commented:
The original suggestions I received were not working for me.  Before I received a corrected solution, I decided to try MS-SQL to write the query and link it back to the MS-Access database.  This worked for me.

Glen
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.