I want to retrieve the data which is "n" moths old.
To compare that I want use only SYSDATE without timestamp. I want to use standard timestamp '23:59:59' along with SYSDATE.
The condition should be as below.
UPDATE_DATE <= ADD_MONTHS(15/01/2013 23:59:59,-3)
UPDATE_DATE <= ADD_MOTHS(30/01/2013 23:59:59,-4)
UPDATE_DATE<=ADD_MONTHS(sysdate||' '||'23:59:59',-3);
the following proble.The emp table is having 14 records.
SELECT * FROM emp ORDER BY empno;
EMPNOENAMESALDEPTNO
7369SMITH80020
7499ALLEN160030
7521WARD125030
[code]...
The emp table is having 10 records.
SELECT * FROM emp_10 ORDER BY empno;
EMPNOENAMESALDEPTNO
7369SMITH80020
7499ALLEN160030
7521WARD125030
7566JONES200020
[code]...
I have written the following PL/SQL block logic tofetch the records from the emp table and compare the records with emp_10 table to perform insert if the records are newelse to perform update the existed records in the emp_10 table.
DECLARE
CURSOR tranche_balance_cur
IS
SELECT empno,
ename,
sal,
[code]...
Execution scenario 1:
I have commented insert and update statements in that case I got the following out put.
Inserted Records4
Updated Records10
As per the logic it's giving the correct output because the cursor is fetching 14 records in that already 10 records are existed in emp_10 tableand 4 are new records.so that it's showing the count for inserted records as 10 and updated records as 4.
Execution scenario 2:
I have uncommented insert and update statements in that case I got the following out put.
Inserted Records13
Updated Records1
As per the logic it's not giving the correct output.
I tried with using TRIM function in the comparision logic to avoid spaces.
TRIM(emp_10.empno) = TRIM(tranche_balance_rec.empno)
AND TRIM(emp_10.ename) = TRIM(tranche_balance_rec.ename)
AND TRIM(emp_10.sal) = TRIM(tranche_balance_rec.sal)
AND TRIM(emp_10.deptno) = TRIM(tranche_balance_rec.deptno)
My scenario is like
i need to insert and retrieve the date fields in
ISO 8601 compliant standard ie..YYYY-MM-DDThh:mm:ss±hh:mm.
I have many locations to get reflected.
From ETL to Oracle, I have stored the timestamp in varchar2 as '30-MAY-11 06.30.00.000000 PM'. Now I need to convert this varchar2(timestamp format) to date. I used:
select TRUNC('30-MAY-11 06.30.00.000000 PM','dd/mm/yyyy hh24:mi') from dualBut, it doest work. get date format?
i have create one standard Calender from that i pickup month date and year separately like 2/6/1987 now i want to convert it into standard date format how to convert it and pass to another block....
View 3 Replies View RelatedHow to extract date from a timestamp data
for example
Input-15/06/2010 05:30:00.000000
output: 15/06/2010
I get data from one table into a gridview and one of the columns is a date. I get it with date and time stamp but I want solely the date in my gridview.
This is what I get: 2009-08-04 00:00:00
This is what I want: 2009-08-04
how to convert date into UNIX time in oracle ?
View 1 Replies View RelatedI want to write a query to get the time stamp from only one date column,
I tried using a group by clause but getting error "not a group by exp."
Below is the query
SELECT ProdID,ProdRequestID, SUBSTR((max(EVENTTIMESTAMP) - min(EVENTTIMESTAMP)), 18,2)Execution_Time
FROM LOG_TIMESTAMPS where ProdID = 1680988889
group by ProdRequestID
ProdID||ProdRequestID ||EVENTTIMESTAMP
[code]....
In the above i am looking for a diference on ProdRequestId,
Output
ProdIDProdRequestIDEVENTTIMESTAMP
16809888892013-04-09T02-56-34.5025kqcxy03
How can i get just date from the timestamp data type.
Suppose i have a column timestamp with has data like "2011-05-16 16:19:22.579764-07" when i select from table i just want the date like 2011-05-16.
I have a timestamp field in an oracle table. The data in that field looks like this.
19-MAY-2011 10.55.21.628206000 AM
I want to query the data in this field by only date portion (not the time portion).
Something like this.
Select * from mytable where archivedate = to_date('19-may-2011','dd-mon-yyyy')
this query doesn't return any data. But actually there is data for 19-may-2011 (along with time portion) in that field.
how to query based on only date portion?
how to extract DATE from timestamp column
View 2 Replies View RelatedI have one command compare varchar type with date type by Pl/SQL.
date_entry gave values:" 2012/01/06 22:28:24",
now i want to convert it to date and compare with sysdate.
Select * From qvsystem
Where to_char(to_date(date_entry, 'rrrr-mm-dd hh24:mi:ss'), 'dd-mm-rrrr') = to_char(Sysdate, 'dd-mm-rrrr');
----
note:date_en2012/01/06 22:28:24
after I executed that command It's appear one message .
How can i separate the Date and time into two separate fields from the Oracle TIMESTAMP?
View 1 Replies View Relatedi wanted to compare a date in one of my tables to sysdate. I have a table reservation and a field in it is Date Reserved From, i wanted to compare this to sysdate and returned the results
View 2 Replies View RelatedI'm trying to compare a date and time within a certain range.
If the sysdate date/time range falls within the range of the values within the database tables then show a "Yes", otherwise, show a "No". The date works but the time doesn't seem to work. Maybe you can't use a "between" operator for time?
CREATE TABLE REGISTRATION
(
EARLY_REGISTRATION_START_DATE DATE,
EARLY_REGISTRATION_END_DATE DATE,
EARLY_REGISTRATION_START_TIME DATE,
EARLY_REGISTRATION_END_TIME DATE
[code]....
Due to some business requirements a table field needs to change from date to timestamp in order to handle the millisecs.
1>When i alter the row , for a table with 150 million recs will there be a conversion. Is there a recommended way to convert the field. Mind you this field is used as a part of composite PK.
2> There is a interfacing application which connects and copies the data to its system and is using the date type, will that application be able to continue to work without any changes, if it does not care about the millisecs.
3> Will there be performance impact on an existing application that uses the date field to sort
4> Will DB need more space due to the change
I have a column where I have time stamp with date and time(8-May-10 10:20:55 Am) from that i will get only time(10:20:55 Am)[query for this mentioned below] Now i need to append the time to current date and insert into a Timestamps column only .
so my result should be 17-May-11 10:20:55 Am(Assuming current Date is 17-May-11) . I tried search it by i found a way in sql server by using DataAdd and DataDiff .
query to get the time is:select to_char(to_date('11-MAY-11 05.00.00 PM', 'DD-Mon-YY HH:MI:SSAM'),'HH24:MI:SS') "Time Now" from dual
I have table named purchage with 2 columns (order_no number,order_date date) in my database. I want to load the data from a file into that table. The below is the file format
100,4/3/2013 1:18:18 AM
101,4/3/2013 1:18:18 AM
102,4/3/2013 1:18:18 AM
103,4/3/2013 1:18:18 AM
104,4/3/2013 1:18:18 AM
105,4/3/2013 1:18:18 AM
106,4/3/2013 1:18:18 AM
how to load the date filed along with the time stamp.
I have a table with two columns called startsem and gradsem they are both CHAR(3). Within those colums are rows that are listed as SemesterYear. For example, F09 is Fall 2009, S09 Spring 2009, and M09 is Summer 2009. I would like to create a constraint that says GradSem must be greater than StartSem b/c no one can travel back in time to graduate. However, as you know you can compare S09 > F09 because it will treat it as a string. I thought I could use a substring and compare the last two digits as a year and that would work but how do I compare the semesters as a time frame? Because in my schema F > S because Spring 2011 comes before Fall 2011 but in reality F < S because to Oracle it is a string and the ASCII value of F is less than S. I cannot chage the coding of the database so editing the rows so they are more date friendly is not an option.
So how can I modify this database to acruately compare StartSem and GradSem.
What is the difference between the following . In my schema all are giving the same results with some different format
SQL> SELECT sysdate , current_date , current_timestamp , localtimestamp from dual;
SYSDATE CURRENT_DATE CURRENT_TIMESTAMP LOCALTIMESTAMP
----------- ------------ ------------------------------------------------- -------------------------------------------------
5/30/2012 8 5/30/2012 8: 30-MAY-12 08.27.22.037703 AM -04:00 30-MAY-12 08.27.22.037703 AM
To rewrite this sql which is in ANSI 92 standard to ANSI 89 standard.
SELECT "PROJECT"."X_SAMPLED_DATE", SAMPLE"."SAMPLE_NUMBER"
FROM "SHIRE_PRD"."LimsUser"."SAMPLE" "SAMPLE" INNER JOIN "SHIRE_PRD"."LimsUser"."PROJECT" "PROJECT" ON"SAMPLE"."PROJECT"="PROJECT"."NAME"
WHERE ("SAMPLE"."SAMPLE_TYPE"='EM' OR "SAMPLE"."SAMPLE_TYPE"='WATER') AND "SAMPLE"."STATUS"<>'X' AND("PROJECT"."X_SAMPLED_DATE">={ts '2011-05-01 00:00:00'} AND "PROJECT"."X_SAMPLED_DATE"<{ts '2011-06-01 00:00:00'})
ORDER BY "SAMPLE"."PRODUCT"
my table have four cols such as
start_value trans_value result_value create_time
1 4 5 20101018 22:33:40
5 -2 3 20101018 22:33:40
3 3 6 20101018 22:33:40
6 -4 2 20101018 22:33:40
2 +3 5 20101018 22:33:40
5 9 14 20101018 22:33:40
but the data is not in the logic order they are confusing and there no origina sequence such as
start_value trans_value result_value create_time
1 4 5 20101018 22:33:40
3 3 6 20101018 22:33:40
5 -2 3 20101018 22:33:40
2 +3 5 20101018 22:33:40
5 9 14 20101018 22:33:40
6 -4 2 20101018 22:33:40
how to get they in logic order.now i have to create a combination in mem and use loop to get the logic order.
I have to implement a new logic such that , Old logic which pulls data will stay in place.now New logic pull data form SAP . So i have implemented the new logic in to the existing cursor as below.
i.e.
Cursor new_sap
old_query
Union
New_query;
I have 3 new cursor like dis. How to process these old and new cursors in FOR loop.. Ex:
For c1 in C2(order_number)
if order_source is sap then ( dis filters old data which is not in SAP)
new query in cursor should process
else
old query in cursor should process
end if;
How will i do this programatically.
i have three tables: ot_req, ot_po_breakup, and ot_po. when a row is inserted into the ot_po table reffering to ot_req,there is a trigger which creates a seperate reocrd in ot_po_breakup with the details of ot_req ot_req.ri_sys_id as pb_ri_sys_id and ot_req.ri_qty as pb_ri_qty ,pi_sys_id as pb_pi_sys_id .upto this part is okand when i insert also the logic is okay.
i have created a trigger to update the rows in the ot_po_breakup table after insert on ot_poin order of pb_ri_sys_id and pb_pi_sys_id and try to update the values in the columns pb_ves1q, pb_ves2q, and pb_ves3q order. i am trying to take the quantities in ot_po.pi_qty and insert them into the pb_ves1q, pb_ves2q, and pb_ves3q columns of ot_po_breakup where those columns are empty (0 or null) such that the sum of those three columns for that row does not exceed the pb_ri_qty in that row.
My problem is i need to mofify my trigger to do the update and delete , that is whenever the user is updating the column ofot_po.pi_qtythe qty should in ot_po_breakup should get updated accordingly and sum of pb_ves1q,pb_Ves2q and pb_ves3q should be equal to pb_ri_qty and do the same for delete as well
CREATE TABLE OT_REQ
(
RI_ITEM VARCHAR2(20 BYTE),
RI_SYS_ID NUMBER,
RI_QTY NUMBER
[code]...
what is the right syntax to use the below decode logic.
Decode(EXTRACT(MONTH FROM sysdate) > 2, ltrim_blanks(GLAMOUNTS.CR_AMOUNT_02) + ltrim_blanks(GLAMOUNTS.DB_AMOUNT_02), 0)
I need to run a query for students that took ALL the following courses: 6710, 6711, 1032, 1035 and ANY of the following courses: 3061,3065
here is my query:
=SELECT distinct student_number, last_name, first_name, gender, ethnicity, students.grade_level, lunchstatus, course_name, course_number ,termid
from students inner join storedgrades on students.id=storedgrades.studentid
where students.schoolid='0976111'
and (students.grade_level >'9' and students.grade_level <= '12')
and (course_number = all('6710', '6711', '1035')and course_number=any('1031','1032','3061','3065','3062', '3401', '3082'))
order by last_name, first_name, grade_level, course_number
The problem is that I'm not getting any results but I know for a fact that some students took the required courses. On a side note, if I change the ALL to ANY - I do get results - but it is not what I want because it would be hard to keep track of the students that met the requirements.
Table Name: F_SCENARIO
System : Dataware house
Oracle version : 11g
Record Count : 2 Million records
Correct scenario records
F_Key F_Bridge_key Record_type
1 1 1
2 1 2
3 1 3
Wrong scenario records
F_Key F_Bridge_key Record_type
1 1 1
2 -5 2
3 -6 3
I want to write a Merge statement to update the negative values into 1.
I have a BEGIN Block of the code where a loop is running .I also have a CONTINUE statement in the EXCEPTION Block of the code where I return the control to the BEGIN Block in case of any exception.Now as per ORACLE Company, CONTINUE has a old issue which is already registered by Oracle Company Bug 7306422.
The issue is where CONTINUE statement does not run properly when your code Optimization level is 2. However it will work properly if we have the Optimization Level as 0 and 1.are facing the same issue here and have a workaround.
Is there any code which I can permanently use instead of CONTINUE but have the same logic incorporated.i.e returning to the next iteration whenever I say to pass the control.
i am trying to upgrade my existing system (6i) to 11g. I have installed Weblogic 10.3.5 with forms and reports 11.1.2.1.0 on windows 7 32 bit. I can connect to my database (10g) from Form builder.
But when I try to open a form/pll of 10g or 6i, it says PDE-PLI018 could not find library and it shows the path of my earlier folder, which now does not exist.
I have made changes in registry FORMS_PATH, FORMS_BUILDER_PATH, UI_ICON And in default.env file FORMS_PATH and CLASSPATH
I have added entry to tnsnames.ora, through which I can connect to DB. And have followed below site to configure weblogic and FM forms and reports;
[URL]
1) Do we have to connect (create a bridge) to DB from weblogic except tnsnames.ora.
2) For library error I think I am missing some settings to be done.
3) If I am able to rectify above error then can I directly use [URL] and access the system from another pc, or still there are some modifications needed.