SQL & PL/SQL :: Extract Minute From Time?
Sep 7, 20101) Extaract time from date field
2) Extract minute form that time ..
ADVERTISEMENT
SQL & PL/SQL :: Extract Minimum Report Time
Oct 1, 2013I have the below scenario:
[u]Report_Time M I Ta[/u]
02-SEP-13X Y35167005
01-SEP-13X Y35931902
03-SEP-13X Y35931901
The output I am expecting from above is:
X Y 01-SEP-13 03-SEP-2013 35931902 35931901
II need to extract minimum report time , max report time and corresponding Ta.
SQL & 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.
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');
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?
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
JDeveloper, Java & XML :: Date Time Omits Time Part
Jun 4, 2012When I try to extract the date tag value from XML data, the time stored in 20120602153021 format i.e., YYYYMMDD24HHMISS format. The following statement extracts only date as 02-JUN-12 however do not extract the time part.
If I try the same in SQLplus with to_date it works however fails in PL/SQL.
XML data:
<?xml version="1.0"?>
<RECORD>
<REGTIMESTAMP>20120601130010</REGTIMESTAMP>
</RECORD>
PL/SQL Extract:
CURSOR c_xml_record
IS
SELECT extract(value(d), '//ACTIVATIONTS/text()').getStringVal() AS REGTIMESTAMP,
FROM t_xml_data x,
[code].......
How To Set Interval Time 4hrs In Dbms_jobs But Starting Time 3:00 Am
Jul 25, 2013how to set interval time every 4hrs in dbms_jobs but starting time 3.00am.
i am set trunc(sysdate)+4/24. but it will take starting at 12.00,4.00,.....in this way..
Fetch Time Greater Than Execute Time?
Dec 27, 2010I have one inline view query which shows exec\ fetch : 2 sec\ 19 sec It gives 500 rows as final out put, when i give rownum<100 it shows exec\ fetch : 1 sec\ 000 sec, and i cannot use this rownum< 100 alternative as this is inline subquery of big query.
What does this exec and Fetch time is?
How to improve fetch time, (esp with sub-query) ?
SQL & PL/SQL :: Get End Time By Query Between Start Time And Duration
Jun 16, 2013I Have three field and first field for START TIME ,Second END TIME & Third DURATION AND Putting START TIME AND END TIME i am getting duration in minutes by using code
""SELECT TO_CHAR
(TRUNC (SYSDATE)
+ (TO_DATE (:T_DONATION_END_TIME, 'HH24MI') - TO_DATE (:T_DONATION_START_TIME, 'HH24MI')),
'HH24MI')
INTO :T_DONATION_DURATION
[code].......
PL/SQL :: How To Check If Time Is Closer To Start Or End Time
Jan 31, 2013I have a table which stores apointment start times and appointment end times. For the sake of this thread I will call them appt.start_time and appt.end_time. I then have a check in time and a check out time for the customer. The only thing is they ONLY way to distinguish between a check in time and a check out time is which one has the earlier time and which one has the later time. Obviously the earlier time will be the check and the later time will be the check out.
This is fine, however sometimes they may forget to check a person in or out and I need to determine whether the time should be insert into the check_in column or the check_out column. To do this I was thinking of comparing the time with the appointment start and end time and if it was closer to the appointment start time put it into the check_in column and if its closer to the appointment end time put it into the check_out column. But I was wondering how I would go about doing this.
The time I will want to compare against the appointment start and end time I will store in a variable called v_time and have this as part of my query, im just unsure of what way to write the query so as to check if the time is closer to the start or end time.
SQL & PL/SQL :: Oracle - Compare End-time With The Start-time?
Jun 7, 2010. I have this query:
select asl1.agentsessionid, asl1.endtime, asl2.starttime, 127 as agentstatus
from
(
select asl1.agentsessionid as sessionid1, min(asl2.agentsessionid) as sessionid2
from cti.agentsessionlog asl1
[code]...
As you can see from my where statement I want to compare the endtime with the startime. This query returns zero results. Is there a way to write the where statement different so I can have results?
SQL & PL/SQL :: How To Extract Data Between [ ]
Jun 8, 2012I have a requirement where the data between [] or ][ has to be extracted from a string.
Here is my situation :
INPUT:
[abc] [def]-[ghi][jlk]
OUTPUT:
row_num field_name
1 abc
2 <blank_space>
3 def
4 -
5 ghi
6 null
7 jkl
SQL & PL/SQL :: Extract One Schema's DDL
Apr 22, 2011Is there any option available in DBMS_METADATA.GET_DDL in such a way that I can extract the script (user creation+grants)only for that particular schema?
View 5 Replies View RelatedSQL & PL/SQL :: Extract DDL Of DBMS_JOB
Dec 20, 2011How to extract DDL of DBMS_JOB in sqlplus ?
View 30 Replies View RelatedSQL & PL/SQL :: How To Extract CSV File
Jun 14, 2010I have to extract a csv file running a sql file.
SQL>@d: estEndItem_Vio_Item_Material_Violations.sql;
This works on the sql prompt. I have to do the same using schedular now for which I want to do the same embedded in a procedure.
create or replace procedure test_csv
as
begin
@d: estEndItem_Vio_Item_Material_Violations.sql;
end;
How can I run the sql file in a procedure.
PL/SQL :: Extract Value From Column Name
Nov 14, 2013I'm stuck on 1 scenario
I have the following table:
Create tabledrop table age_rate;
CREATE TABLE age_rate(age_0_4 NUMBER(4),age_5_20 NUMBER(4),age_21_34 NUMBER(4),age_35_44 NUMBER(4));
-------------------------------Insertion
INSERT INTO age_rateSELECT 45, 50, 60, 90
FROM dual UNION ALLSELECT 45, 50, 60, 88
FROM dual UNION ALLSELECT 40, 50, 60, 90 FROM dual UNION ALLSELECT 5, 50, 60, 88
FROM dual ;
-------------------------------Query on table
SELECT * FROM age_rate; Query Output age_0_4 age_5_20 age_21_34 age_35_44 45 50 60 9045 50 60 8840 50 60 905 50 60 88 Required outputRate Min_age Max_age
----The below rate is for age band 0_445 0 445 0 440 0 45 0 4
--The below rate is for age band 5_2050 5 2050 5 2050 5 2050 5 20
--The below rate is for age band 21_3460 21 3460 21 3460 21 3460 21 34
--The below rate is for age band 35_4490 35 4488 35 4490 35 4488 35 44
Rules--I have all data in rows so each column in row create separate rows and add 2 columns automatically Min_age and Max_age and insert value on these column on basis of column name for example if column name like age_0_4 then put 0 in min_age and put 4 in max_age means values for Min_age and Max_age extract from the basis of column name. I don't know if it is possible or not
PL/SQL :: Query To Extract Changes
May 7, 2013I am using Oracle database version 11.2.1 and would like to extract the level change and level start date where reason_code is 'PROMO' split by ID.
The test script is below:
create table test(
id number,
start_date date,
reason_code varchar2(10),
level number
)
insert into test values(001, '01-JAN-13', 'PROMO', 2);
[code]....
The expected output would be:
Fields - ID, old_level, old_level_start_date, new_level, new_level_start date
e.g.
001, 2, '08-MAR-13' , 3, '05-MAY-13'
002, 4, '13-APR-13' 5, 02-MAY-13'
PL/SQL :: Extract Numbers Only
Sep 18, 2012Here i face probelm that he numbers must be follw by DOT "." , this is not correct if the statment only conatines numbers without DOT that not extract. As the
SELECT REGEXP_SUBSTR ( 'hello to 8898989898989 jkjk nnnm mnj'
, '([0-9]+.[0-9]*)' || -- Starts with digit(s) (may or may not have digits after .)
'|' || -- or
'(.[0-9]+)' -- starts with decimal point
) AS result
FROM dual
;
but mean i have to add . after numbers . as follow
SELECT REGEXP_SUBSTR ( 'hello to 8898989898989 jkjk nnnm mnj'
, '([0-9]+.[0-9]*)' || -- Starts with digit(s) (may or may not have digits after .)
'|' || -- or
'(.[0-9]+)' -- starts with decimal point
) AS result
FROM dual
;
but this is not right
i want to extract numbers without DOT also.
Extract All Employees Who Have 2 Functions
Feb 24, 2013I have a table:
create table employee_function
(
id_emloyee number,
id_function number
);
with clients and their functions.
I want to extract all employes who has 2 functions (ex:id_function = 1 and id_function=2)
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-
How To Extract The Data From DBF Files
Aug 13, 2010I'm a SAP consultant working in SQL on NT platforms. This is the first conversion from Oracle that I have done. My client has provided us with a "Cold" backup of the Oracle dbase on a HD formatted in Unix, I have the partition mounted and I'm able to view the files. I have the ORDATA folder with all the .DBF files.
Q: How do I extract the data from the .DBF files. I need to export to something workable with SQL.
Original database was on Unix, I'm operating on Windows platform.
Creating Extract File From DB?
Jun 22, 2010I have data in multiple oracle tables. I have to create a extract flat file after applying some validation and business logic on it and store it in unix server with naming convention FF_RMS_SC_<<YYYYMMDDhhmm>>.txt.This job will be scheduled to run daily to create the flat file. I guess pl/sql and unix needs to be used.
View 1 Replies View RelatedUnable To Extract NULLS
Dec 20, 2011Am trying to extract data from oracle onto flatfile(.txt), am using UTL_FILE but, NULLs in oracle tables are getting converted into space and if i try loading into table it is getting loaded as space,
View 1 Replies View RelatedExtract DDL's Without Storage Parameters?
May 11, 2005I need to extract DDL's without storage parameters. if i use the export and import using indexfile or if i try to extract using the DBMS_METADATA.GET_DDL package , in both ways my output is with the storage parameters
eg:
CREATE TABLE "SEPT"
( "DEPTNO" NUMBER(2,0),
"DNAME" VARCHAR2(14),
[Code].....
Extract Index DDL From Database?
Apr 27, 2011Is there a way, i can extract index DDL from my database?
View 1 Replies View RelatedSQL & PL/SQL :: How To Extract Substring From A Set Of Strings
Jan 25, 2012Im trying to extract, *THIS IS MY STRING* from *<YUVRAJ THIS IS MY SRTING YUVRAJ>* .
In this <YUVRAJ and YUVRAJ> is constant, need to remove which is being appended at begin and end for a set of strings.
SQL & PL/SQL :: How To Extract The Rights Of Users On ERP
Mar 2, 2013I would like to extract the user rights from the ERP that I use with SQL/Oracle.
There are 3 databases. The first gives me all the users with the main key id_user. The second one gives me the actions with the main key id_action. The third one only gives me what the user can't do.
I would like to know all the actions that all users can do or not.
The result must be something like:
Action 1
User 1 Yes
User 2 No
Action 2
User 1 No
User 2 Yes
....
SQL & PL/SQL :: Extract Date From String
Jan 8, 2013I need to extract the First portion of date entered in remarks column , i have prepared a test as below.
CREATE TABLE LEAVE_DYS (LV_EMP_CODE VARCHAR2(12), LV_DESC VARCHAR2(2000));
INSERT INTO LEAVE_DYS VALUES ('R0754','LEAVE SALARY FOR APRIL 16, 2010 - APRIL 15, 2012 (33 ABSENTS)');
INSERT INTO LEAVE_DYS VALUES ('R0667','LEAVE SALARY FOR JULY 14, 2009 - JULY 13, 2011 (44 ABSENTS)');
INSERT INTO LEAVE_DYS VALUES ('R0841','LEAVE SALARY FOR MAY 29, 2010 - MAY 28, 2012 (NO ABSENT)');
INSERT INTO LEAVE_DYS VALUES ('R0854','LEAVE SALARY FOR MAY 29, 2010 - MAY- 28, 2012 (1 ABSENT)');
[code].....
--Required output is
LV_EMP_CODE LV_DESC
R0754 16/04/2012
R0667 14/07/2012
R0841 29/05/2012
R0854 29/05/2012
R0313 09/09/2012
SQL & PL/SQL :: Extract String Between The Braces?
Sep 13, 2011I have following column data
NZ(abc_mode)/ (NZ(bch_mode) + NZ(cdh_mode)
Now i need query to extract the string inside the braces NZ() with or without using regular expressions in SQL
the output should be
abc_mode
bch_mode
cdh_mode