PL/SQL :: Creating A List Of Dates And Hours
Jul 24, 2013
I have the following code which will produce a list of dates -
SELECT to_date('01-Jan-2013','DD-Mon-YYYY') + rownum - 1
FROM all_objectsWHERE rownum <= to_date('31-Jan-2013','DD-Mon-YYYY') - to_date('01-Jan-2013','DD-Mon-YYYY') + 1 01/01/201302/01/201303/01/201304/01/2013.....30/01/201331/01/2013
But what I'm looking for is a way to also include all possible hours with the dates, so my output would be like -
01/01/2013 0001/01/2013 0101/01/2013 0201/01/2013 03...31/01/2013 2131/01/2013 2231/01/2013 23
View 5 Replies
ADVERTISEMENT
Jul 24, 2013
I need a similar function to determinate difference between two dates, but i need other business hours; Monday - Friday: 9:00 - 21:00 (this is OK)Saturday: 09:00 - 14:00 (and this is my problem, how to add this condition in this function)
View 4 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
Mar 22, 2007
I've been assigned a task at work that consists of creating a trigger on a table. This table is used to store temporary query results. I'm trying to make a trigger to clean the table so old results don't accumulate.
There is a column named DATE_UPDATED that stores an Oracle Date for when the row was inserted/updated. If the row is not updated in 12 hours, I want to delete it.
Here is what I have so far.
CREATE OR REPLACE TRIGGER clean_tableName
AFTER INSERT OR UPDATE
ON tableName
BEGIN
END clean_tableName;
As you can see, I don't have an actual function body yet. What would be the best way to accomplish this? Should I declare a variable to store the current Date+12 hours and then just compare that in a delete statement?
I'm thinking something like:
DECLARE
laterDate DATE := SYSDATE+12; // Not sure how to add 12 hours to a date.
BEGIN
DELETE FROM tableName WHERE DATE_UPDATED > laterDate;
END clean_tableName;
But I'm not sure how to add 12 hours to the current date. Is there a way I can do this without defining a variable?
View 2 Replies
View Related
Nov 3, 2011
creating sql query for producing result as per below from dual table.
SELECT to_char( sysdate,'MON-RRRR')
FROM dual
where sysdate between '01-APR-2011' and '31-MAR-2012'
order by 1 desc
from above query the result is
NOV-2011
but i need the result as per below
APR-2011
MAY-2011
JUN-2011
JUL-2011
AUG-2011
SEP-2011
OCT-2011
NOV-2011 CURRENT MONTH
View 4 Replies
View Related
Jul 9, 2013
Oracle Developer Suite 10g
I want to create List item with time .
like in pic when I click on upper side at list item increase time and other side decrease time.
View 1 Replies
View Related
May 19, 2007
How can I create a list of items in a field, for instance lets say I have a table called car and one of the sub categories is parts. How can I make it so that parts can be any number of pre-defined entities? Or even table, for instance if I make a table called parts how can I use that in the car table in place of parts?
My second question is about using foreign keys as primary keys. If I am writing an email sql DB and I decided to use the members usrname as the primary key in the member table but then made another table that lists all the emails and decided to make the foreign key member username the primary key there.
Is that safe to do or should i create a sequence in which to identify a primary key for the email list table? Also what if I extend member to several other tables and use it as a primary key there too, seems like a kind of dangerous way to do things...
View 1 Replies
View Related
Jul 31, 2012
based on a provided list of key values, joining the collected list against a source table to retrieve additional information related to the key. In this simple example, the procedure accepts a list of employee numbers. The goal is to print a list of names associated with those numbers.
The method is to materialize the list of employee numbers as rows and join those rows to a source table to get the names. I have used BULK COLLECT. Why we cannot cast PLSQL tables using a type defined in the procedure's specification (why the type needs to exist as an object before we can cast it, like this:
SELECT * FROM TABLE ( CAST ( SOME_FUNCTION(&some parameter) AS SOME_TYPE ) );
here is my demo SQL, which you should be able to execute against the SCOTT schema without any change
declare
type employee_numbers is table of emp.empno%type index by binary_integer;
type employee_names is table of emp.ename%type index by binary_integer;
type employees_record is record (empno employee_numbers, person_name employee_names);
records employees_record;
[code]...
View 12 Replies
View Related
Jan 26, 2011
I want to find the dates which have a date plus with in 2 days after this date. I mean group by 3 days each even the date i missing between two days. Actualy I want to find the start date where the employ was missing on job.
Basic concept is employes have allowed to use 10 personal leaves of a year. Each leave can be use for maximum 3 days.
If employ did not come on the job for one day or two days or three days, it shoul be count as ONE personal leave. And If employ is missing at job for four or five days, it should be count as 2 personal leaves.
seq date
------------------------------
101.01.10
205.01.10
306.01.10
410.01.10
512.01.10
613.01.10
714.01.10
815.01.10
916.01.10
1018.01.10
1119.01.10
1220.01.10
1321.01.10
1423.01.10
1526.01.10
1627.01.10
1729.01.10
1831.01.10
The result should be (Don't use Pl/Sql)
seq date
------------------------------
101.01.10
205.01.10
310.01.10
413.01.10
516.01.10
619.01.10
723.01.10
826.01.10
929.01.10
After finding these days I want to select the starting date of 5th personal leave. (which is 16.01.10).
I am not a expert of using SQL, but I think it could be possible with using partitioning a table on the givin reslult and further partition the reslut on rownum() as rn and the using case statement where rn = 5.
View 2 Replies
View Related
Mar 10, 2011
Split a date into new dates according to black out dates!
Here is my tables:
CREATE TABLE travel
(
start_date,
end_date
)
AS
SELECT DATE '0000-01-01', DATE '9999-12-31' FROM DUAL;
[code]....
I have lets say a "travel date" and black out dates. I will split the travel date into pieces according to the black out dates.
Note: Travel Date can be between 0000-01-01 - 9999 12 31
Sample:
Travel Date:
Travel | START DATE | END DATE
T | 2011 01 04 | 2011 12 11
Black Out Dates:
BO | START DATE | END DATE
A | 2010 11 01 | 2011 02 11
B | 2011 01 20 | 2011 02 15
C | 2011 03 13 | 2011 04 10
D | 2011 03 20 | 2011 06 29
Excepted Result:
New Travel | START DATE | END DATE
X1 | 2011 02 16 | 2011 03 12
X2 | 2011 06 30 | 2011 12 11
Visually:
Travel Date : -----[--------------------------]--
A : --[------]-------------------------
B : ------[---]------------------------
C : --------------[---]----------------
D : ----------------[------]-----------
Result :
X1 : -----------[--]--------------------
X2 : -----------------------[--------]--
Sample 2:
Travel Date : -[--------------------------------]--
BO Date A : ----[------]-------------------------
BO Date B : -------------------------[---]-------
BO Date C : ----------------[---]----------------
BO Date D : ------------------[------]-----------
Result X1 : -[-]-------------------------------
Result X2 : -----------[--]--------------------
Result X3 : -----------------------------[--]--
How can I do it using PL SQL ?
View 5 Replies
View Related
Oct 22, 2003
How can I select all of the dates between two dates? For example, given the start date 12/2/2003 and the end date 12/5/2003 I want to return:
12/2/2003
12/3/2003
12/4/2003
12/5/2003
Is there a built in function for this? Is there a way for a function to return multiple rows? It has to be a function because I need to use it within other SQL statements.
View 14 Replies
View Related
Jan 10, 2011
I want to write a function that gets:
1. event_date (dd/mm/yyyy)
2. event_start_time (??/??/???? HH24:MI)
3. event_end_time (??/??/???? HH24:MI)
I got a table called EVENTS that got 2 fields:
1. event_date (dd/mm/yyyy HH24:MI)
2. event_end_date (??/??/???? HH24:MI)
and want to check if there is an event in my EVENTS table that occurs in the same dd/mm/yyyy as the input, and can disturb the input event times. means:
input.event_start_time is between EVENTS.event_date
and EVENTS.event_end_date
and
input.event_end_time is between EVENTS.event_date
and EVENTS.event_end_date
but to compare only the hours here! (HH24:MI)
because the date (dd/mm/yyyy) is checked before..
I don't know how to cut only the hours out of the date and compare them, and don't know how to write the whole function.
View 2 Replies
View Related
Sep 27, 2011
I am creating the Dynamic list but when i am compiling the form it gives the compilation error "No list elements defined for the list item".
I can eliminate it by entering the dummy list element but this dummy value will be displayed at form run time.
View 1 Replies
View Related
Dec 14, 2011
Im trying to list the products list of a client grouped by type of the product. Ex:
product type
prod.A acid
prod.B flavour
prod.C acid
prod.D cleaner
prod.E flavour
I want to list something as:
Acid
Prod.A
Prod.C
Cleaner
prod.D
Flavour
prod.B
prod.E
View 1 Replies
View Related
Oct 30, 2011
DECLARE
CURSOR GRP IS
SELECT RowNum rn, Letter_Group_ID||'-'||A_Desc AName,Letter_Group_ID
FROM Hrs_Group;
BEGIN
Clear_list('Letter_Group_ID');
FOR I IN GRP LOOP
Add_List_Element('Letter_Group_ID',I.rn,I.AName,I.Letter_Group_ID);
end loop;
END;
FRM-30351: No list elements defined for list item.
List LETTER_GROUP_ID
View 4 Replies
View Related
Dec 27, 2010
I have a table which will the following type of data
"COL1""COL2"
1001"27-DEC-2010 02:00:00"
1002"27-DEC-2010 07:00:00"
1003"27-DEC-2010 09:00:00"
1004"27-DEC-2010 02:00:00"
1005"27-DEC-2010 12:00:00"
Here you can see that we have data for 27th Dec 2010 02,07,09 and 12 hours. I want a query which will show the full 24 hours data even if it doenst have any records. like the following,
COL1 COL2
0 2010122701
1001 2010122702
1004 2010122702
0 2010122703
0 2010122704
0 2010122705
0 2010122706
1002 2010122707
View 2 Replies
View Related
Aug 15, 2011
I have a field in Customers table called shipeddate....
I wnat to check the number of hours an item which has a shipeddate is in the store room to the current datetime...
But the business hrs of the store room are from 8am-5pm..
So when a shipped date is 4pm on MOnday
and i am checking on 9 am Tuesday the number of hrs shud be 1(4-5 of Monday)+1(8-9 of tuesday) =2hrss..
How can i achieve this...
View 2 Replies
View Related
Oct 22, 2012
i 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.
View 3 Replies
View Related
May 8, 2010
I am using the below sql query to calculate working hours. The problem which i am facing is that query is taking lot of time to calculate the working hours. reduce the execution time of this query or if there is any other way to calculate working hours
The following query take 63.499 sec
SELECT sql_calc_found_rows gstime,
MAX(stoptime) AS mx,
MIN(starttime) AS mn,
[Code].....
View 6 Replies
View Related
Jan 1, 2013
I want to convert below MS-SQL query in oracle 10g.
for eg:
Select numasdate,
Cast(numasdate / 60 as Varchar) + ' hours ' +
Cast(numasdate % 60 as Varchar) + ' minutes'
as [TotalHoursAndMinutes]
From
#SampleTable
Output:
9436 157 hours 16 minutes
537 8 hours 57 minutes
9323 155 hours 23 minutes
12525 208 hours 45 minutes
View 8 Replies
View Related
Oct 4, 2012
I have a table with a date field. This field storage dates with hours like this:
01-08-2012 8:30:00
01-08-2012 8:15:00
01-08-2012 9:30:00
01-08-2012 9:40:00
02-08-2012 8:30:00
02-08-2012 9:30:00
02-08-2012 9:34:00
...
I have to generate a report with the day and the frequency :
__Day_______ Hour Range__CountRecords
WEDNESDAY 8 - 9 2
WEDNESDAY 9 - 10 2
THURSDAY 8 - 9 3
The block of the hour must be 1 hour (8-9,9-10 and so on)
how generate the hours range.
My database oracle version is 8i. (I know that is very old but I can't change because It´s a legacy system).
View 5 Replies
View Related
Jun 14, 2012
I am not getting added hours from results.
SELECT audittimestamp + interval (SELECT EXTRACT(TIMEZONE_HOUR
FROM systimestamp) FROM DUAL) hour from tab1I want to add Timezone_hour to my timestamp.
View 7 Replies
View Related
Feb 16, 2011
Recently we have upgraded from 11.1 to 11.2 . But after upgrade SQL statements that are running fine in 11.1 was running for hours in 11.2. Statistics are collected 100%...
View 2 Replies
View Related
Mar 11, 2013
I am fetching data which are older than 2 hours from now. TRANSACTION_TIME is varchar2 field in "MM/DD/RRRR HH:MI:SS PM" format.
SELECT * FROM TRANSACTION_DETAILS
WHERE TO_DATE(TRANSACTION_TIME,'MM/DD/RRRR HH:MI:SS PM') <(SYSDATE - 2/24);
Is there anything wrong in the query.
View 2 Replies
View Related
Sep 19, 2010
format of dtActivityStartDate/dtActivityFinishDate: 2010-09-17 14:50:51.150 Note: Both dtActivityStartDate/dtActivityFinishDate
vcActivityName = Process Request
usdFuncTimeCalc (vcActivityName,dtActivityStartDate, dtActivityFinishDate)
i need to calculate time elasped for that type of activity following are the rules:
(If Process Request is the activity)
Working Days: Monday through Saturday
Hours of Operation: 9AM 5PM
only working hours of day need to the counted like for example if it is sep 15 11 Am is dtActivityStartDate & Sep 17 is dtActivityFinishDate is 10 Am. then time elapsed is 11am to 5pm on sep 15 , 9 to 5 on sep 16 & 9 to 10 on sep 17 so total should be
6+ 8 + 1 = 15 hours + minutes.
format of date time: 2010-09-17 14:50:51.150
vcActivityName = Process Request
Don't worry about process request..
View 4 Replies
View Related
Nov 1, 2013
I want to validate dynamic hours value using decode in sql...
If value is greater than 24PM it should display some statement...
if value is less than 0AM it should display some statment...
View 10 Replies
View Related
May 26, 2010
Check the following
SQL> ALTER SESSION SET TIME_ZONE = '-01:00';
Session altered.
SQL> SELECT SysTimeStamp
2 FROM dual;
SYSTIMESTAMP
[code]...
My doubt is that why Extract(HOURFROMCURRENT_TIMESTAMP) is showing 11 rather than 10?
View 4 Replies
View Related
Feb 1, 2011
I am using Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
I have 2 schemas in my application.
1. Application schema
2. EOD(End of day) schema.
End of day schema is populated from Application schema whenever user runs EOD process. The tables are pulled like this.
1. Master tables : Always deleted and reinserted at each EOD process
2. Log tables for each transaction table: Delta between the last EOD and current EOD data is pulled and are used for populating transaction tables
3. Transaction tables: These are populated from log tables pulled from previous step. The logic is like this
Now based on these tables about 30 reports are generated in EOD schema. Please note that each transaction table will have a EOD_ID and any report generated uses the where condition <transaction table>.EOD_ID = <current EOD_ID>
My log table contract_log and transaction table is contract in EOD schema.
contract_log table has data like this
contract_number contract_date customer_idqtyrateeffective_date
11/Jan/2010CUST-0110101/Jan/2010
1NULLNULLNULL112/Jan/2010
EOD on 1st Jan 2010 constructs contract table as
contract_numbercontract_datecustomer_idqtyrateeod_id
11/Jan/2010CUST-011010EOD-1
(Since the change of rate 11 is not visible on 1st Jan 2010 because it is effective on 2nd Jan 2010)
EOD on 2nd Jan 2010 constructs contract table as
contract_numbercontract_datecustomer_idqtyrateeod_id
11/Jan/2010CUST-011011EOD-2
(Since the change of rate 11 is visible on 2nd Jan 2010)
This logic is working fine. But we run more than 20-30 EODs the processing time increased to 10-15 hours.
It took some time to figure out the issue as a single query when run from toad or pl sql developer runs in few seconds but as a part of the whole package it takes 2-3 hours(each query).
The problem found was that oracle execution plan gets corrupted when the process starts. So what we did was to analyze the tables
after they are pulled. This perfectly solved our problem. Currently the whole process is taking only about 12-13 minutes where about 3 minutes is lost on analyze tables and indexes. I know this is a temporary solution as I need to get out of online analyze of tables and indexes.
My code for table and index regeneration is as below
PROCEDURE sp_gather_table_index_stats(pc_table_name VARCHAR2) IS
CURSOR cur_ind IS
SELECT index_name
FROM user_indexes
WHERE table_name = pc_table_name;
BEGIN
EXECUTE IMMEDIATE ' begin DBMS_STATS.gather_table_stats(user,' || '''' ||
pc_table_name || '''' || '); end;';
FOR cur_ind_rows IN cur_ind LOOP
EXECUTE IMMEDIATE ' begin DBMS_STATS.gather_index_stats(user,' || '''' ||
cur_ind_rows.index_name || '''' || '); end;';
END LOOP;
END;
View 1 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
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
