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?
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.
0
PatHartmanCommented:
IIf(Format(YourTimeStamp, "hh:nn") >= #7:00# and Format(YourTimeStamp, "hh:nn")  <= #19:15#, "Day Shift", "Night Shift")
0
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")
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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
0
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?
0
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
0
Gustav BrockCIOCommented:
It should read:

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

/gustav
0
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
0

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.
0
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
0
Rey Obrero (Capricorn1)Commented:
@GPSPOW,
you should have mentioned in your original post that you are accessing SQL tables.
0
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.
0
Gustav BrockCIOCommented:
Mine was not air code and it works.

/gustav
0
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)
0
Gustav BrockCIOCommented:
It is never "the best way" to use magic numbers.

/gustav
0
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
0
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
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.