Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 773
  • Last Modified:

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?
0
wdelaney05
Asked:
wdelaney05
1 Solution
 
ianmills2002Commented:
In Oracle this is easy

(lab_test_end_dttm - trunc(lab_test_end_dttm) - 17/24) * 24 *60

This calc will get you the amount of minutes past 17:00
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now