Link to home
Start Free TrialLog in
Avatar of W D
W DFlag for United States of America

asked on

Convert datetime field to trunc([datetime field] + '17:00'

Hi,
I need to convert a date time field to the truncation of that date plus '17:00' for every value of that date time field.
This is why I need to do it. A shift time ends at 5:00 pm every day (Sunday through Saturday).
Lab tests may go past 5:00 pm every day and be counted as overtime for an employee. I need to take my date time field, lab_test_dttm (which is the date AND time) truncate it and concatenate '17:00' to it and then subtract lab_test_end_dttm, my lab test ending dttm field, from it. I think.
So, say, my lab_test_end_dttm is 17:45 for a particular day. My truncated lab_test_dttm at 17:00 minus 17:45 is 45 minutes of overtime for that day.    
How would I do the trunc lab_test_dttm at 17:00 thing for this calculation?
ASKER CERTIFIED SOLUTION
Avatar of ianmills2002
ianmills2002
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial