PL/SQL :: Get Time In Hours Minutes And Seconds Subtract Between Two Varchars?
Oct 18, 2012
I have two varchar variable which has value like this
v_outpunch1:='17:50:00'
and v_Shifttime:='18:00:00'
this both time i am subtracting here and storing in another varchar variable which is like this.
v_EarlyLeaverstimeformat := LPAD((extract(hour from TO_TIMESTAMP (v_ShiftTime,'HH24:mi:ss')) - extract(hour from TO_TIMESTAMP (v_OutPunch1,'HH24:mi:ss'))), 2, '0')||':'|| LPAD((extract(minute from TO_TIMESTAMP (v_ShiftTime,'HH24:mi:ss')) - extract(minute from TO_TIMESTAMP (v_OutPunch1,'HH24:mi:ss'))), 2, '0')||':'|| LPAD((extract(second from TO_TIMESTAMP (v_ShiftTime,'HH24:mi:ss')) - extract(second from TO_TIMESTAMP (v_OutPunch1,'HH24:mi:ss'))), 2, '0');
it's not subtracting value correctly.
View 3 Replies
ADVERTISEMENT
Oct 6, 2010
I want to convert numbers into hours and minutes.I have two numbers say like first number is 1234 and second number is 1235.4 now i want to find the different between these two numbers. so am subtracting number 2 - number 1
1235.4 - 1234 i will get 1.4 so the result of this 1.4 to be converted as hours and minutes like 1 hr and 40 minutes. How can i convert numbers in hrs and minutes.
View 7 Replies
View Related
Feb 11, 2011
I currently have a problem where I have two date fields with time stamps. The only bit i am currently interested in in these fields is the time factor. When i display them in their field they have a format of HH24:MI .
I have a start time and end time as well as a duration and duration type. What I am trying to do is the following: when the user inputs the start time, along with the duration say 1 for example and the duration type of say HRS for example I would like to have the end_datetime default to 1 HR from the current start time. This is the code I use on a when validate item trigger to acheive this:
case :blk.duration_type
when 'HRS' then
:blk.end_datetime := :blk.start_datetime + ((1/24)* :blk.duration);
when 'MINS' then
:blk.end_datetime := :blk.start_datetime + ((1/24/60)* :blk.duration);
However, every time it triggers the value put into end_datetime is 0:00 is it something to do with the datatypes im using .
View 1 Replies
View Related
Feb 10, 2012
I am trying to covert a time shown as 001200 (12 hours) to 720 mins. I have tried the following but get errors when executing:
(MTTD)>=TO_DATE('000000','HH24:MI:SS').
I have also tried .
MTTD = to_date('000000','HH24:MI:SS') =trunc(sysdate,'MM')*24*60; and neither is working.
What am I doing incorrectly?
View 1 Replies
View Related
Jun 14, 2012
How to get Time Difference between two DateTime Columns in Oracle 10g ?
View 10 Replies
View Related
Mar 31, 2010
I need below proc like...
procedure p1
(
i_time_min number -- minutes to be substracted from timestamp
)
is
v_end_timeinstamp timestamp(6);
begin
[code]....
The problem with above procedure is passing parameter is in minutes and i need to substract the same from sys_extract_utc(current_timestamp) and store result in v_end_timeinstamp in timestamp format only... substracting directly will reduce the days and not the minutes.
View 6 Replies
View Related
Nov 5, 2010
I am able to run this query in Sql Prompt.
DEFINE Today = TO_DATE('03.12.2004:10:34:24','DD.MM.YYYY:HH24:MI:SS')
SELECT TO_CHAR(hiredate,'DD.MM.YYYY:HH24:MI:SS') "Hiredate",
TO_CHAR(&Today,'DD.MM.YYYY:HH24:MI:SS') "Today",
trunc(86400*(&Today-hiredate))-60*(trunc((86400*(&&Today-hiredate))/60)) "Sec",
[Code]...
It gives proper / desired results. I want to use it on my Form, how can it be done?
View 3 Replies
View Related
Jan 14, 2012
I have a field " Tran_date " with datatype Date . It contains date as well as time . How can I get the time . I tried with it as below :
SELECT TO_CHAR(TRAN_DATE,'DD-MON-YYYY HH24:MI:SS') FROM ABC
WHERE COMP=1 AND
BRANCH=1 AND
LOC_CODE='12' AND
TRAN_DATE='12-JAN-2012' ;
VALUE IS COMING LIKE THIS :
12-JAN-2012 00:00:00
ALTHOUGH THERE IS TIME . Its not zero .
View 3 Replies
View Related
Jul 9, 2010
I have a query to add two numbers and get results in hours:minutes format.Example I want to add 12.20 and 6.15 and get result in hours and minutes like 18.35 (hours & minutes).if minutes that is after precision exceed more than 60 it should treat as 1 hour.like i want to add
12.35 (number 1) before precision its hour and after its minutes
06.25 (number 2)
04.25 (number 3)
-----
23.25 (23 hours and 25 minutes)
-----
View 6 Replies
View Related
Mar 24, 2012
I want to find the hours and minutes between two char field data type.Example I have two char columns one is "start_time" and another one is "end_time".The start time and end time is the machine reading of CNC MACHINE in manufacturing.I want to develope the package to capture the actual machine running time.But I have the start and end time reading in character field.The machine reading format is hour:minutes:seconds only.It will looks like 1234:45:23(the machine ran 1234 hours and 45 minutes and 23 seconds).See the below table to understand my requirements.
start_time end_time result in hours & minutes
---------- -------- -------------------------
345 347 2 hrs
347 350 3 hrs
350 357.20 7 hrs and 20 minutes
If I subtract end_time - start_time I will get the result in char type only not in hours and minutes format.Another example
start_time end_time result in hours & minutes
---------- -------- -------------------------
357.21 360.40 3.19(If subtract end_time - start_time)
View 2 Replies
View Related
Sep 28, 2011
SELECT emp.emp_id, emp.ename, TRUNC (attendancedatetime),
TRUNC ( ( 86400
* (MAX (attendancedatetime) - MIN (attendancedatetime))
)
/ 60
)
- 60
[Code]...
Following is the out put:
EMP_IDENAME Date Min Hrs
10013Javed Iqbal09/20/2011 00:00:0036.007.00
10013Javed Iqbal09/21/2011 00:00:0027.007.00
10013Javed Iqbal09/22/2011 00:00:0049.007.00
10013Javed Iqbal09/23/2011 00:00:000.000.00
10013Javed Iqbal09/24/2011 00:00:000.000.00
i need the TOtal sum of Minutes and Hrs e.g 7+7+7=21 and also minutes but if minutes total increase from 60 minutes then it should add to hrs .how to get sum.
View 2 Replies
View Related
Nov 29, 2006
is there a prebuilt function that will round say the time of a sysdate up or down 5 mins? so i entered 5:32pm i would want it to round it down to 5:30pm
View 1 Replies
View Related
Oct 15, 2012
I have two columns which I need to add together then devide by 60 in order to display that time in minutes. the problem I am facing is that at times I get numbers above 60 and my client doesn't want to see numbers above 60.
i.e (col1 + col 2)/60 = 34.87
what I need to do is make sure that when the number reaches 60 it moves on to 35.
View 2 Replies
View Related
May 26, 2013
how to pull data from a table where date is greater than current time (+24 hours)... my date field is in the following format 15-MAR-2013 20:07:00
I want to do something like this
select * from table_A where date_field > (sys_date_time) +24h
as an example, when I run a query @ 4 PM on March 26, I want to pull data that has date > 4 PM March 27
View 6 Replies
View Related
Dec 26, 2012
Need to check the elapse time for particular query ran from last 24 hours , it was ran multiple times and need to know for each execution what is elapse time .
which view we can use ?
View 5 Replies
View Related
Mar 14, 2012
I am trying to write a trigger to reduce 1 day from 4 date fields of a table (order)
table name = order
fields = date_1, date_2, date_11, date_12 and dest_id
for example: the trigger will verify if orders that are dropping is for a specific customer (dest_id). if yes, the trigger will subtract one day from these date fields (by updating them); if no, (the dest_id is not for this customer) the trigger will not update anything in the DB.
View 1 Replies
View Related
Jul 6, 2010
I want to substract two dates to get the age of a person. One of the date is sysdate and the other is date of birth. Assuming we remove the time part of the date.
View 16 Replies
View Related
Sep 17, 2012
How to subtract Time_Out - Time_IN
SELECT EMP_CODE, TO_DATE(INTIME,'DD/MM/RR')IN_DATE,
MIN(TO_CHAR(INTIME,'HH24:MI')) TIME_IN,
MAX(TO_CHAR(OUTTIME,'HH24:MI')) TIME_OUT
--MAX(TO_CHAR(OUTTIME,'HH24:MI')) - MIN(TO_CHAR(INTIME,'HH24:MI'))
FROM HCM.einout
WHERE EMP_CODE ='470' AND INTIME >= '01-APR-12' AND INTIME <= '15-APR-12'
GROUP BY EMP_CODE, TO_DATE(INTIME,'DD/MM/RR')
ORDER BY 2 ASC
[code]....
View 5 Replies
View Related
Apr 18, 2012
Oracle version:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
how to subract the timestamp values.
SELECT TO_TIMESTAMP ('10-Sep-02 14:10:10.123000', 'DD-Mon-RR HH24:MI:SS.FF') FROM DUAL;
This is my output.
TO_TIMESTAMP('10-SEP-0214:10:10.123000','DD-MON-RRHH24:MI:SS.FF')
---------------------------------------------------------------------------
10-SEP-02 02.10.10.123000000 PM
Now how to subtract the FF value in sysdate.(ex:10000 this is the input of FF value )
example output like: "18-APR-12 09.46.44.005196 AM"
View 10 Replies
View Related
Nov 13, 2012
New to using Oracle and SQL Developer. I am trying to subtract a maximum date from today and adding back 1 to get a field named daysSinceLastActivity....
Syntax I am trying is (date()-max(activity_date))+1 as daysSinceLastActivity
I keep getting a error message of missing expression. After googling, I am not finding anything. This should be a simple calculation.
View 3 Replies
View Related
Nov 9, 2011
I have a date column in a table, I need to fetch records from 27:11:11 00:00:10 to 30:11:11 00:00:10 (DD:MM:YY HH:MI:SS). i.e between seconds/min/hrs.
expecting Something like this,
SELECT * FROM TABLENAME WHERE MODEDATE BETWEEN
TO_CHAR('27/11/11 00:00:10','DD-MN-YY HH24:MI:SS') AND
TO_CHAR('30/11/11 01:10:10','DD-MN-YY HH24:MI:SS')
View 6 Replies
View Related
Feb 11, 2011
What is the exact usage of the SUBSECONDS in Timestamp data type.
Especially it has range upto 9 decimal places?
View 4 Replies
View Related
Oct 23, 2012
I have a date field which is passed as in parameter defaulted to sydate With the passed date as input I need to run an eod for every 15 mins by calculating
v_sdate := TRUNC ( in_sdate
- (MOD (TO_CHAR (in_sdate, 'MI'), 15) * 60)
/ (24 * 60 * 60),
'MI'
);
v_edate := TRUNC (in_sdate, 'MI');
Using the above v_sdate and v_edate I am calculating the start date and end date and compare these in my final select query.Now in case of rerun of old date, I am calculating start date as : v_sdate := TRUNC (in_sdate, 'MI') - 15 / 1440;
But in the above calculation, I am not considering seconds, but I need to consider those also, because say I ran the eod at 23-oct-2012 12:23:13 then my start date in case of re run should start from 12:23:14 secs , how can I achieve that?
View 3 Replies
View Related
Oct 30, 2008
i'm getting ORA-04091: error when using a trigger to subtract ncopies values of table (rental ; after i insert a NEW record in it ) from cno value of table (copies) to give me the remaining no of copies of a book .
my tables and code is as folllows:
1)COPIES;
CID CNO BOOK
----- ---------- --------------------
A1 5 BOOK1
B1 10 BOOK2
2)rental;
Name Null? Type
------------------------------- ---
RID NOT NULL VARCHAR2(5)
CID VARCHAR2(5)
NCOPIES NUMBER(2)
1 CREATE OR REPLACE TRIGGER NO_COPIES
2 AFTER INSERT ON RENTAL
3 FOR EACH ROW
4 DECLARE
5 K VARCHAR2(5);
[code]....
View 3 Replies
View Related
Oct 23, 2013
SQL> SELECT MAX (upd_time), MIN (upd_time), COUNT (serial)
FROM (SELECT * FROM trans
UNION ALL
SELECT * FROM trans_archive);
MAX(UPD_T MIN(UPD_T COUNT(SERIAL)
--------- --------- -------------
23-OCT-13 01-JAN-11 5289261
I need to calculate seconds between MAX (upd_time) and MIN (upd_time) and then calculate trans/sec. Number of trans COUNT (serial).
SQL> desc trans;
Name Null? Type
----------------------------------------- -------- ----------------------------
SERIAL NOT NULL NUMBER(11)
UPD_TIME NOT NULL DATE
MESSAGE NOT NULL VARCHAR2(255 CHAR)
ENTITY_TABLE NOT NULL VARCHAR2(32 CHAR)
ACTION NOT NULL VARCHAR2(12 CHAR)
[code]....
trans_archive the same DDL.
my first try to get intervall between max and min date in secons:
SQL> SELECT EXTRACT (DAY FROM (MAX (UPD_TIME) - MIN (UPD_TIME))) * 24 * 60 * 60
+ EXTRACT (HOUR FROM (MAX (UPD_TIME) - MIN (UPD_TIME))) * 60 * 60
+ EXTRACT (MINUTE FROM (MAX (UPD_TIME) - MIN (UPD_TIME))) * 60
+ EXTRACT (SECOND FROM (MAX (UPD_TIME) - MIN (UPD_TIME)))
DELTA
FROM (SELECT * FROM TRANS
UNION ALL
SELECT * FROM TRANS_ARCHIVE);
SELECT EXTRACT (DAY FROM (MAX (UPD_TIME) - MIN (UPD_TIME))) * 24 * 60 * 60
*
ERROR at line 1:
ORA-30076: invalid extract field for extract source
View 18 Replies
View Related
Feb 13, 2012
I ran this query :
SELECT el.date_time_stamp AS occurance_time,
esl.ack_time_stamp AS response_time,
esl.res_time_stamp AS resolved_time,
NVL ( (esl.ack_time_stamp - el.date_time_stamp), 0)
AS time_taken_to_acknowledge,
NVL ( (esl.res_time_stamp - el.date_time_stamp),0 )
[code].....
View 4 Replies
View Related
Jan 2, 2013
I have to create the following table. The fields Trend_Date, Price and Trend are already given. I have to calculate the field permanently and to insert the value in this permanent table.
Fields:
The field price belong to the value of a product during the trade.
The field trade_date belongs to the moment of the trade.
The field trend belongs to the future behavior of the the price. Here, the price of the present moment is compared to the following price (possible characteristics: 'UP', 'DOWN', 'STABLE').
The field permanently belongs to the time (in seconds) how long the value of the field Trend_Date (depending on the price) is still true.
For example:
Row 1: The trend in row 1 is 'UP' and it has a price of '11'. Until row 3 this remains true (the price is greater or equal to 11). In this case, the difference between row 1 and row 3 are 9801 (rounded) seconds.
Row 2: The trend in row 2 is 'DOWN' and it has a price of '12'. This remains true till to the end (the price is never greater than 12) In this case, the difference between row 2 and row 11 are 97346 (rounded) seconds. To calculate the 97346 seconds the field has to consider that between row 2 and row 11 are two days. There will be no trade between 18:00 and 07:00 o'clock. This belongs to 7 hours for each days, in seconds (2*46800) 93600.
-> 190945-93600 = 97346s
Row 6: The trend in row 6 is 'UP' and it has a price of '5'. This remains true till to the end (the price is never smaller than 5) In this case, the difference between row 6 and row 11 are 65729 (rounded) seconds. To calculate the 65729 seconds the field has to consider that between row 65729 and row 11 are one days. There will be no trade between 18:00 and 07:00 o'clock. This belongs to 7 hours for each days, in seconds (1*46800) 46800.
-> 112528-46800 = 65729s
Row 9: The trend in row 9 is 'STABLE' and it has a price of '8'. Until row 10 this remains true (the price is equal to 8 ). In this case, the difference between row 9 and row 10 is 14418 (rounded) seconds.
Row 11: Is empty because there are no values to compare.
Example Table
TRADE_DATE --PRICE --TREND --permanently
02.01.13 11:21:42,720000000--11--UP--9801
02.01.13 12:44:03,236000000--12--DOWN--97346
02.01.13 14:05:03,845000000--11--DOWN--92485
[Code]....
View 16 Replies
View Related
Mar 1, 2012
Our database was generating archivelog(50MB) every 30seconds! I think this is not normal because what I did is open our database, I was the only one who is connected, I'm not running anything, but our database is still generating archivelogs!
Our redo logs: 6groups 3members.
This are the things I saw on our alert logs:
- advanced to log sequence
- cannot allocate new log, sequence
- checkpoint not complete
- private strand flush not complete
What I did is change the log mode of our database to noarchivelog then open the database, then returned it to archivelog mode then that fixed the problem. But the thing is after 6hours its abnormal behavior goes back again.
View 3 Replies
View Related
Oct 8, 2012
select to_char(SYSDATE,'MMDDYYYYHHMISS') from dual;
Ouput:
10082012010338
It is giving me correct output, but i want to display even the Fraction of seconds, how can i?
I have tried this..but not working..
select to_char(SYSDATE,'MMDDYYYYHHMISSFF') from dual;
Output: Error.
View 4 Replies
View Related
Apr 11, 2013
I'm using Apex 4.2.1 against Oracle 11gR2 and mod_plsql.create a date picker item that allows users to select seconds as well as hours and minutes.
I have searched this Forum and see that others have asked this question. The answers have all been "No". However, I've seen no such question since version 4.1 has been released, and so, am hoping there now is a way to do this.
I have adjusted the Date Format field to be "DD-Mon-YYYY HH24:MI:SS", both under the date picker item itself as well as under the "Global" parameters section of my application. All to no avail. The date picker shows only hours and minutes for the time portion.
View 2 Replies
View Related
