Convert datetime field to trunc([datetime field] + '17:00'
Posted on 2013-05-20
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?