PL/SQL :: How To Convert Minutes To Hour And Minute
Oct 4, 2012i want to convert minutes that is 90 want to convert it in hours and minute that format should come in 01:30 format.
can i get in this format?
ADVERTISEMENT
Replication :: Daily Refresh At Particular Hour / Minute?
Apr 28, 2009However, I need to refresh the group manually sometimes. Therefore, I cannot set the interval as sysdate+1.have tried setting the interval as follows. However, they are not correct
Quote:trunc(sysdate+1) +4/24
The next interval will show 9:25:20pm.
Quote:trunc(sysdate+1) + interval '4' hour
Incorrect syntax
SQL & PL/SQL :: Subtract Two Numbers And Convert Result As Hours And Minutes
Oct 6, 2010I 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.
SQL & PL/SQL :: Get Difference And Convert Integer To Time In Hours And Minutes
Jun 14, 2012How to get Time Difference between two DateTime Columns in Oracle 10g ?
View 10 Replies View RelatedSQL & PL/SQL :: How To Get Correct Minute Between Two Hours
Oct 22, 2012i am using one query but not getting correct minutes.
here is my query:
v_Interval:= to_timestamp(v_temphrs,'HH24:MI:SS')-to_timestamp(v_outpunch1,'HH24:MI:SS');
v_TotalHrsMin1 := extract(hour from v_interval) * 60 + extract(minute from v_interval);
here v_interval datatype is "interval day to second" and v_temphrs datatype is varchar2 and value is : 12:00:00 and v_outpunch1 datatype is varchar2 and value is: 06:10:00
and v_totalHrsMin1 datatype is number.
here i should get value 370.
but i am getting value 350.
SQL & PL/SQL :: Extract Minute From Time?
Sep 7, 20101) Extaract time from date field
2) Extract minute form that time ..
Forms :: Play Sound After 1 Minute?
Oct 28, 2012I've sound item, I want to play sound on it every one minute automatically without doing any event.
Go_Item('ITEM');
Read_Sound_File('C:UsersAAADesktopSOUND.wav', 'WAVE', 'ITEM');
Play_Sound('ITEM');
Hour Must Be Between 0 And 23
Jan 2, 2008follow SQL query would lead to an ORA 01850: hour must be between 0 and 23 error? I'm getting this error don't not sure if this query is causing it.
select
mlh_date_begin,
mat_desc,
mlh_alert_text,
mat_code
from s_mlh_mem_alert_history,
s_mat_member_alert_type
where mlh_mem_uid=f_mem_uid
and mlh_date_begin <= trunc(sysdate)
[Code]...
ORA-01850 - Hour Must Be Between 0 And 23?
Oct 4, 2007I am receiving the following Oracle ERROR:
HIPDAO -> getXAlerts():SQLExceptionORA-01850: hour must be between 0 and 23
HERE is the code where it's occuring:
public List xMethod(
int memUID)
throws DAOException {
[Code].....
The error doesn't make sense because I don't think I'm setting a date anywhere in the code.
SQL & PL/SQL :: AVERAGE By Hour
May 9, 2010I wish to query the pro_vitual_bytes by hourly average by server name from 04/01/2010 to 04/02/2010. how i going to perform the query.
SERVER_NAME DATE_TIME PRO_VITUAL_BYTES
MLYORABA04/01/2010 00:00:402,238,312,448.00
MLYORABA04/01/2010 00:01:402,241,458,176.00
MLYORABA04/01/2010 00:02:402,238,312,448.00
MLYORABA04/01/2010 00:03:402,241,458,176.00
[Code]....
PL/SQL :: Every One Hour Update Of Xml
Apr 15, 2013After prepared this xml . I make some edit(like add two record) in my emp and dept table i need every one hour update of my xml . How i will do this one ?
select xmlelement("AllDepartments",
xmlagg(
xmlelement("Department", xmlattributes(d.deptno as deptno, d.dname as dname),
xmlagg(
[code].........
Extract Hour From A Date?
Mar 29, 2004how to extract hour part from a date?
suppose my date is like
29-mar-2004 09:20:34
i wanna get only hour from the above date-
Redo Generated In Last 2 Hour
Feb 7, 2013Is there any way to get the amount of redo generated in last 2 hour. which has below chareteistic
1. redo generated by currently connected session from last 2 hour.
2. redo generated by session disconnected during last 2 hour.
total_redo = disconnected sessoin during last 2 hour + connected session generating redo during last 2 hour.
Number Of Waits Reaches To 100 In 8-hour Snapshot
Oct 8, 2013in the awr report I saw some segments in Segments by ITL Waits section. Number of waits reaches to 100 in 8-hour snapshot. Is it small/big number? Should I consider increase INITRANS or there is nothing to be worried because value 80-100 are not too high?
View 3 Replies View RelatedSQL & PL/SQL :: How To Manipulate Greater Than 24 Hour Clock In Oracle
Jul 14, 2011I have an Oracle application that deals with a 29 hour clock. so the days begin at 5am instead of midnight. I am trying to get data from a table where time is up to certain number of hours, but when it reached the 00 clock my whole query returns no records, even though there is plenty of records before the midnight hour, because the data is saved as lets say 2530 instead of 0130
I am using this
SELECT *
FROM WORK.WORK_UNIT
WHERE (to_char (current_garage) = :entry_blk.curr_garage
OR 'all' = :entry_blk.curr_garage)
AND route >= 0 AND run >= 0
AND ((package_id > 0 and work_code = 99) OR
(package_id is null and work_code = 1))
and Nvl (Emp_No, 0) = 0
AND work_date = :entry_blk.p_work_date
AND on_time <= TO_CHAR(SYSDATE + :Entry_Blk.up_to_hour/24, 'HH24MI')
since oracle deals with a 24hour clock, my code doesn't seem to work if there is data for after midnight (00 hour). I am using :Entry_Blk.up_to_hour/24 to determine up to how many hours I want to see data, i.e.
on_time <= TO_CHAR(SYSDATE + 2/24, 'HH24MI')
will give me data up to 2 hours
SQL & PL/SQL :: Time Range Include A Specific Hour?
Aug 11, 2010I'm looking for a way to say if a time range includes a specific hour.
If I was looking to work out if 01:00 to 04:00 includes 02:00, then this works:
CASE WHEN TO_DATE('02:00','HH24:MI') BETWEEN TO_DATE('01:00','HH24:MI') AND TO_DATE('04:00','HH24:MI') THEN 1 ELSE 0 END
But, if the time go over two days (with midnight in between), it doesn't work:
CASE WHEN TO_DATE('02:00','HH24:MI') BETWEEN TO_DATE('18:00','HH24:MI') AND TO_DATE('04:00','HH24:MI') THEN 1 ELSE 0 END
Generating ADDM Reports Automatically Every Hour?
Jun 9, 2012I have been trying to develop a script for generating ADDM Reports every hour and save it in a directory on the server. I was able to develop a script to run the AWR Reports for every hour and save them in a directory, but I ran into troubled waters in the ADDM script.
Database Version : Oracle Database Enterprise Edition 10.2.0.3
OS : IBM AIX 5.3
I'm trying to debug the script below to generate ADDM reports on a per hour basis and save them in a folder as well as mail than to a particular entity.
########################################################################################
# Set up Oracle environment variables...
#------------------------------------------------------------------------------
export PATH=/usr/bin:/etc:/usr/sbin:/usr/ucb:/oracle/app/oracle/product/10.2.0/bin:/usr/bin/X11:/sbin:.:/oracle/app/oracle/product/10.2.0/bin
export ORACLE_HOME=/oracle/app/oracle/product/10.2.0
export ORACLE_SID=sarcotest2.ora
export ORAENV_ASK=NO
[Code].....
Initially, the script did return me an ADDM report, but the problem was that it generated the report for only a set of two snapshots (eg: 410110 and 410111). later when the snapshots advanced further, it still generated the same report for the same snapshots.
Now, it seems it have made several changes to the script in the declare and dbms_advisor section and the reports are not being generated anyway.
One problem I am facing is
ORA-13605: The specified task or object ADDM_UAT does not exist for the current user.
ORA-06512: at "SYS.PRVT_ADVISOR", line 2043
ORA-06512: at "SYS.DBMS_ADVISOR", line 560
ORA-06512: at line 1
It seems the task is not being generated at all in my user's schema.
Creating AWR Report Including Current Hour?
Jun 4, 2013In an 11g database is it possible to create an awr report including the current hour or do I always have to wait till the full hour?
View 1 Replies View RelatedPL/SQL :: Query Returning Zero Results For Yesterdays Same Hour
Apr 22, 2013I have the following query:
SELECT
d_dtm,
BTS_ID,
CASE WHEN D_DTM = (D_DTM-24/24)
THEN sum(V_ATT_CNT)
[Code]....
But it is not returning any results because of the "having" clause. I know it should return results because all I want it to do is in one column have the V_ATT for the current time period, and in the 2nd column, have the V_ATT 24 hours ago. I've checked the data and I should get results back but can't seem to figure out why this is not working...
Reports & Discoverer :: Recover From Hour Glass Symbol
Mar 21, 2011I have logged into Oracle and Discoverer several times and every time when I'm running a report in Discoverer, it quits responding. This is after I already have the data, and I'm scrolling to the bottom of the data to get the next 10,000 rows.
I just get the "hour glass symbol" and then i basically have to shut down and restart my computer.how to recover from these type of hour glass symbol.
Server Administration :: Time Of DB Machines Are One Hour Ahead Of Active Directory
Jun 14, 2012our DB machines time is one hour ahead of Active Directory, as we sync our time with AD, is there any contribution of Database in the wrong time of solaris machines ? and for the resolution of problem, what can be checked at DB side.
View 1 Replies View RelatedPL/SQL :: Return Past Timestamp Values On Base Of Input Hour To User Function
Nov 22, 2012resolve issue while modified the user function code for returns the values as timestamps
---function code
create or replace
function fun_test_timestamp(P_HOUR varchar2) return varchar2
is
sql_stmt varchar2(1000);
begin
[Code].....
Input:-
select fun_test_timestamp('5') from dual;
Output:-
SELECT CURRENT_TIMESTAMP - INTERVAL '5' HOUR FROM DUAL;
Modified Fun Code:
create or replace
function fun_test_timestamp(P_HOUR varchar2) return timestamp
is
sql_stmt varchar2(1000);
[Code]...
Input:-
select fun_test_timestamp('5') from dual;
Output:-
ORA-00911: invalid character
ORA-06512: at "NETVERTEXTRUNK.FUN_TEST_TIMESTAMP", line 8
00911. 00000 - "invalid character"
*Cause: identifiers may not start with any ASCII character other than letters and numbers. $#_ are also allowed after the first character. Identifiers enclosed by doublequotes may contain
any character other than a doublequote. Alternative quotes (q'#...#') cannot use spaces, tabs, or carriage returns as delimiters. For all other contexts, consult the SQL Language Reference Manual.
*Action:
SQL & PL/SQL :: How To Get Minutes Between Two Times
Oct 19, 2012i want one query which return minute between two times which is in this format: 12:00:00 and 06:00:00
so in this it should return 360 minutes.
How To Round Time Up Or Down By 5 Minutes
Nov 29, 2006is 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 RelatedHow To Display Time In Minutes
Oct 15, 2012I 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.
SQL & PL/SQL :: How To Subtract Minutes From Timestamp
Mar 31, 2010I 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.
SQL & PL/SQL :: Count Records Per Every 10 Minutes
Jun 6, 2011How to count records per every 3 minutes ? we don't want SPs to get answer. Instead of we want single query to get this output.
The sample data has been enclosed with it.
SQL & PL/SQL :: Difference Of Dates And Get Minutes
Oct 25, 2012 I have a date in_sdate as In parameter defaulted to sysdate. Basing on this in_Sdate I calculate my start and end dates as:
v_sdate TRUNC (in_sdate, 'MI') - 15 / 1440 ;
v_edate := TRUNC (in_sdate, 'MI');
My procedure is run for every 15 minutes. Now suppose if I am running for old dat, then I should get the difference of dates by taking
v_old_Date := v_edate - in_Sdate;
Divide this by 15 , round that value and loop to run the procedure for that n times. My doubt is when I am saying
v_old_date := v_edate - in_sdate ; I am getting expression is of wrong type. How can I take the difference of the dates and get the minutes from that ?
Archive Log Generated Each 4 Minutes?
Jul 2, 2012in our database 10.0.2.4 with RAC archive log generated each 4 min , did this increase the performance of database and how i can fix it
View 14 Replies View RelatedPL/SQL :: Date Subtraction Of Minutes?
Jun 14, 2012how to get correct result for the time subtraction of minutes for the following query
select (to_date('14-06-2012 11:10:00','dd-mm-yyyy hh24:mi:ss') - to_date('14-06-2012 11:00:00','dd-mm-yyyy hh24:mi:ss'))*60*60 as
result from dual
RESULT
----------
25
i want the output as 10 minutes.