INSERT INTO JOB_EXECUTION (RUN_DATE ,RUN_ORDER ,RUN_DIV ,RUN_TYPE ,RUN_FMT ,PICKUP_STATUS ,START_TIME ,END_TIME ,REMARKS) VALUES
(sysdate-1,1,'AE','FILE-HFM','NY_PLAN-2014','FINISHED',TRUNC(SYSDATE),SYSDATE,'"Load Process is INITIATed: with [BATCHID:= FILE-HFM_RUN_ORDER#_5 ]: as of @ 26-Sep-2013 10:26:50.
AFM-Load END PROCESS: as of @ 26-Sep-2013 10:26:52.
AUTO-LOAD RUNNING :as of @ 26-Sep-2013 10:26:57.
FILEs-LOAD PROCESS: as of @ 26-Sep-2013 10:26:59.
ALLOCATIONs PROCESS: as of @ 26-Sep-2013 10:27:52.
[code]....
The select I tried.
WITH reporting_info AS (
SELECT b.run_date,b.run_div,b.run_type,b.run_fmt,1234 as Batch_id, start_time as BATCH_START_TIME, end_time AS BATCH_END_TIME,
REPLACE(substr(substr(remarks,instr(remarks,'FILEs-LOAD PROCESS: as of @ ')), 0,instr(substr(remarks,instr(remarks,'FILEs-LOAD PROCESS: as of @ ')),chr(10))-2 ),'FILEs-LOAD PROCESS: as of @ ','') FILE_LOAD,
REPLACE(substr(substr(remarks,instr(remarks,'ALLOCATIONs PROCESS: as of @ ')), 0,instr(substr(remarks,instr(remarks,'ALLOCATIONs PROCESS: as of @ ')),chr(10))-2 ),'ALLOCATIONs PROCESS: as of @ ','') ALLOCATION,
[code]....
Since the date format does not contain AM or PM in it ... while subtracting time values crossing midnight the value goes in negative .... How to subtract two date strings with out AM or PM values to get a positive value ?
My problem is that I am working with a balance (Infor/WorkBrain - Time & Attendance) I have a balance called Bank Holiday(bal_id 10044). Every holiday that an employee works, he is allowed to take another day off in lieu of the day he worked. The employee has up to twelve months to take that holiday.
My job is to figure out when he worked that holiday (not a problem) and send an email/note at the 11 month mark and notify the employee and supervisors that said employee has 1 month to use his banked holiday.
My problem is the data/constraints I am working with. I can tell what day the holiday was based on a time code (EBLOG_MESSAGE) and EBLOG_DELTA shows a positive 1. I can also tell when the employee has used a bank holiday (EBLOG_DELTA is -1) and EBLOG_MESSAGE = FR_HWO.
But how to I perform the deduction so that I get four most current dates. Meaning since my first date that the employee gets for a holiday wokred is 6/3/2010 but the employee used a bank holiday on 8/31/2010. So I want to display the dates 7/1/2010, 7/27/2010, 8/1/2010 and 10/15/2010.
My preference would be not to add anything new to this table as it is a core table for Infor/WorkBrain.
I am having a problem when calculating dates in a package usiong Toad. I have my function :
FUNCTION PORTFOLIO_MEMO_RPT_Sql( schema_name IN VARCHAR2 , select_business_date IN DATE ) RETURN VARCHAR2.....SELECT DISTINCT Case when NVL(TO_DATE(''09/24/2013'',''MM/DD/YYYY'') - DECODE(mn.Active, 1, mn.DueDate, sc.DueDate ),0) = 0 then ''0Days'' when NVL(''' || select_business_date || ''' - DECODE(mn.Active, 1, mn.DueDate, sc.DueDate ),0) = 0
then ..... when I give it my variable from outside , it is not working......but when I use a date inside, it works. I call my function from outside like :
SELECT * FROM TABLE ( FI_rpt.Fi_Rpt_Pkg_loan.PORTFOLIO_MEMO_RPT (sys_context('userenv','session_user'),TO_DATE('09/24/2013','MM/DD/YYYY')) )
I have database jobs that upload the data in my applications.My problem is while copying the record of one application to another, the format of dates goes wrongFor example:
The date in one column is 01-JAN-1941 but in the other record while copying ot goes as 01-JAN-2041.
I'd really like to get the difference expressed in hours, minutes and seconds. I tried:
, TO_CHAR(fcr.actual_completion_date, 'HH24:MI:SS') - TO_CHAR(fcr.actual_start_date, 'HH24:MI:SS') diff_But that doesn't work - I get an ORA-01722: invalid number error.
I also tried:
, TO_CHAR(fcr.actual_completion_date - fcr.actual_start_date, 'DD-MON-YYYY HH24:MI:SS') diff_But got an ORA-01481: invalid number format model error
In one of the table, dates are stored in character format. I want to find the ID where start_date is greater than stop date have converted the char to date by using to_date function but still I am getting error.
with t as(select 122 as ID, 'JAN-12' as start_date, 'MAR-13' stop_date from dual unionselect 123 as ID, 'MAR-12' as start_date, 'FEB-12' stop_date from dual unionselect 124 as ID, 'MAR-12' as start_date, 'JAN-12' stop_date from dual unionselect 125 as ID, '01-MAR-1949' as start_date, '13-APR-1947' stop_date from dual unionselect 126 as ID, '01-MAR-1959' as start_date, '13-APR-1970' stop_date from dual unionselect 127 as ID, '11-MAR-1950' as start_date, '13-JAN-1951' stop_date from dual unionselect 128 as ID, '21-JUL-1969' as start_date, '20-JUL-1969' stop_date from dual)select * from tWHERE to_date(start_date) > to_date(stop_date)
i'm creating a website where i can search between 2 specific dates which user will enter in the format of DD-MM-YYYY. the 2 dates will be inserted into 2 fields which is $input1 and $input2.
extract($_POST); //to set the date format //*************************** $input1 = date('DD-MM-YYYY h:i:s'); $input2 = date('DD-MM-YYYY h:i:s'); [code]...
but i get the error message saying...Warning: odbc_exec() [function.odbc-exec]: SQL error: [Oracle][ODBC]Option value changed., SQL state 01S02 in SQLExecDirect in C:phpwwwaaas_bbb.php on line 86...Error in SQL statement
I have a string in this format '2011/06/01 00:00:00'. How do I convert this to date format.I tried to_date,to_char and they give errors invalid number & literal does not math format string. I don't have much control on the string since that is the way is comes from the application.
WHERE TO_DATE(TO_CHAR(RR.PEROFOPFROM,'DD-MON-YYYY')||RR.AIRCRAFTSTD,'DD-MON-YYYY:HH24MI') > TO_DATE(TO_CHAR(RR.PEROFOPFROM,'DD-MON-YYYY')||RR.AIRCRAFTSTA,'DD-MON-YYYY:HH24MI')
I have data like this:
PEROFOPFROMAIRCRAFTSTD
29/03/20102150 NULL NULL NULL NULL 30/03/20102150
When I execute the query it always gives me the error "literal does not match format string".
I'm trying to do data mining on a web log which recorded one day web access information from a busy web server. I imported the data into Oracle Data miner, and created a table (WEBLOG). The idea is to create a new field, i.e. session, for the users so that each session could be thought as a representative of a user-intent (aka topic). Now based on this, data mining models would be used to cluster(group) the users based on their similarity. The first step is to prepare the data which involves using SQL queries. So first, all I did was to create a function for date and time. This is the following code I used,
create or replace function ssndate(p_date in varchar2 default '03-01-18', p_time in varchar2) return number $if dbms_db_version.ver_le_10 $then deterministic $elsif dbms_db_version.ver_le_11 $then result_cache $end as begin return trunc((to_date(p_date||' '||p_time, 'dd-mm-yy hh24:mi:ss') - to_date('01-01-90','dd-mm-yy')) * (86400/2400)); end ssndate; /
The function ssndate compiled successfully.The next step I took was to create a view through the following query,
create or replace view WEBLOG_VIEWS as select (select ssndate(LOG_DATE, LOG_TIME) from dual) as "SESSION_DT", C_IP, CS_USER_AGENT, (CS_URI_STEM||'?'||CS_URI_QUERY) as WEB_LINK from WEBLOG;
This was successful as well. The problem is in the next step where I try to do data grouping.
create table FINAL_WEBLOG as select SESSION_DT, C_IP, CS_USER_AGENT, listagg(WEB_LINK, ' ') within group(order by C_IP, CS_USER_AGENT) "WEB_LINKS" from WEBLOG_VIEWS group by C_IP, CS_USER_AGENT, SESSION_DT order by SESSION_DT [code]....
the to_date function should be fine. In the data that I possess, the date and time are in no format. Example: 30118 and 0:00:09 respectively.
We store the date in String Type format of "MON-YYYY", we require to run the report base on that string contain date column, During the Query execute the Error Display of (ora-01858: a non-numeric character was found where a numeric was expected)
The Query is :
select period_name,doc_sequence_value,doc_sequence_id,date_created,name,JE_SOURCE,XX from ( select period_name,doc_sequence_value,doc_sequence_id,date_created,name,JE_SOURCE,TO_DATE(XX,'DD/MM/RRRR') XX from ( select TO_CHAR(TO_DATE(('01-'||substr(gl.period_name,1,3)||'-'||substr(gl.period_name,5,2)),'DD/MM/RR'),'DD-MON-RRRR') xx,
insert into iostat_mac values(to_date('2012/03/28 08:00:00 AM'),'653.6','20.7','15392.0','451.8','0.0','5.5','0.0','8.2','0','64','/vol/sun_dc_u2');
Receive ora-01861 error when trying to insert.Suspected it had to do with the date format, I changed the nls parameter for my session to include the time format, but to no avail.
UPDATE event_request SET REQ_EVENT_DATE = TO_CHAR(REQ_EVENT_DATE - INTERVAL '1' HOUR, 'yyyy-mm-dd hh24:mi') WHERE eventID=123
Oracle returns this error:
ERROR at line 4:ORA-01861: literal does not match format string
where line 4 is REQ_EVENT_DATE = TO_CHAR(REQ_EVENT_DATE - INTERVAL '1' HOUR, 'yyyy-mm-dd hh24:mi').The REQ_EVENT_DATE field was originally populated with this stripped down query:
I am trying to insert a row in a table and getting the below error.
SQL> insert into tbl_force_charging(ANI, date_time, durations,src, circleid) 2 values ('9569333585','29-JUN-11 03.19.41.000000000 PM','1027','51010','BIR' ) 3 ; values ('9569333585','29-JUN-11 03.19.41.000000000 PM','1027','51010','BIR') * ERROR at line 2: ORA-01830: date format picture ends before converting entire input string
Table Structure is
Name Null? Type ----------------------------------------- -------- --------------------------- ANI VARCHAR2(10) DATE_TIME DATE DURATIONS VARCHAR2(10) SRC VARCHAR2(10) CIRCLEID VARCHAR2(10) SQL>
select profilename from PROFILE where user_data like '%DATE_RULE!115%';
Output will be "Test A".Now, this is just a single value from RULES table used to find the data of PROFILE table.I will have to run the query on multiple values of RULES tables to find records containing a string format of sort "DATE_RULE!<rule_no>". How to search on WILD CARDs like these?
Version: 11.2 I am not an SQL expert. I am trying to subtract the start time and end time and thereby find the difference between these two times. In the below example , the difference between start_time and end_time is around 52 minutes (6:15 PM to 7:07 PM)I tried some stuff to find the difference below. But it didn't work out .
SQL> create table t (start_time date, end_time date); Table created. SQL> desc t Name Null? Type ----------------------------------------------------------------- -------- --------------------- START_TIME DATE END_TIME DATE SQL> insert into t values (to_date('24/JUN/2013 18:15:42', 'DD/MON/YYYY HH24:MI:SS'), to_date('24/JUN/2013 19:07:54','DD/MON/YYYY HH24:MI:SS')); 1 row created. SQL> commit; Commit complete. SQL>SQL>SQL> select * from t; START_TIME END_TIME-------------------- --------------------24 Jun 2013 18:15:42
when i run this query i am facing date format error.
select sbrueregister.UEIMSI,sbrueregister.fapid,sbrfapslid.slid,sbrfapslid.ACTIVATION_TS,sbrfapslid.DEACTIVATION_TS from SBRFAPSLID INNER JOIN sbrueregister ON sbrfapslid.fapid=sbrueregister.fapid where sbrfapslid.slid='1234567890' and sbrueregister.registeredat between TO_DATE('2013-02-1.12.0. 10. 123000000','YYYY-MM-DD HH.MI.SS.SSSSSS')and TO_DATE('2013-02-1.12.9.10.123000000', 'YYYY-MM-DD HH.MI.SS.SSSSSS');
ORA-01830: date format picture ends before converting entire input string
I have a table test with column containing dates, characters and numbers. I have to extract the number part and the three characters before the number . My data looks like :
TEST ID DATA 1 3/12/2007 2 0 3 3/8/2010 ABC 217 4 NONE 5 COLM XYZ 469 6/8/2011 6 LMN 209
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.
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.
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 : ----------------[------]-----------
Travel Date : -[--------------------------------]--
BO Date A : ----[------]------------------------- BO Date B : -------------------------[---]------- BO Date C : ----------------[---]---------------- BO Date D : ------------------[------]-----------
Result X1 : -[-]------------------------------- Result X2 : -----------[--]-------------------- Result X3 : -----------------------------[--]--
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.
I'm facing some problem even after using INSTR function in Oracle.The problem is I have written the logic in the PL/SQL block which appends all the values fetched in a loop on the basis of whether the string is present or not.
For ex:
The first value fetched from the select query first is ABCDEFG which gets appended to a variable The next value fetched is AB even this has to be appended to the variable since this exactly doesn't match with ABCDEFG. The next value fetched is BCDEF even this has to be appended to the variable since this exactly doesn't match with ABCDEFG. The third Value fetched is ABCDEFG this will not get appended presently according to the logic which is correct.
writing that piece of code to append the value fetched which doesn't exactly match with the existing string
I am newbie to oracle and using oracle 10g as database. I want to get dates between two dates .... let me give an example suppose a user enters 1-Aug-2010 - 31-Aug-2010 , so i should get all dates in between from date and to date.
something like "select date..or whatever from dual where date between 1-Aug-2010 and 31-Aug-2010 " like this type or other.
