Solved

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

Posted on 2013-05-20
1
705 Views
Last Modified: 2013-05-21
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
Comment
Question by:wdelaney05
1 Comment
 
LVL 6

Accepted Solution

by:
ianmills2002 earned 500 total points
Comment Utility
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now