How to achieve time in Oracle?
Today, due to the needs of the project, the boss made me responsible for writing the stored procedure in Oracle. Hey, I have never been touched before, this time is a very good learning opportunity, I have a good grasp!
However, during use, encounter a problem, do not know how to achieve time-added functions, because the system needs to be added to the system. Find information through the web, but eventually do nothing. So I decided to write one! I hope I can help my friends!
- Name: add_times - Function: Returns the result of D1 and NewTIME, achieve time-added - Description: For the date in NewTIME, it is not considered - Date: 2004-12-07 - Version: 1.0 - author: Kevin
Create Or Replace Function Add_Times (D1 in Date, NewTime In Date) Return Date
IS
HH Number;
MM Number;
Ss Number;
Hours Number;
Dresult date;
Begin
- When removed, minutes, seconds
SELECT TO_NUMBER (To_Char (NewTime, 'HH24')) INTO HH from Dual;
Select TO_NUMBER (To_Char (NewTime, 'Mi')) INTO MM from Dual;
SELECT TO_NUMBER (To_Char (NewTime, 'SS')) INTO SS from DUAL
- Convert NEWTIME in the middle and hidden sum, percentage of one day
Hours: = (HH (mm / 60) (SS / 3600)) / 24;
- Results after the time is added
SELECT D1 Hours Into Dresult from Dual
Return (DRESULT);
End add_times;
- Test case - select add_times (sysdate, to_date ('2004-12-06 03:23:00', 'YYYY-MM-DD HH24: MI: SS')) from DUAL
