Solved

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

Posted on 2013-05-20
1
732 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 6

Accepted Solution

by:
ianmills2002 earned 500 total points
ID: 39182957
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

734 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