PL/SQL :: Unable To Round Date Function?
Feb 21, 2013cant i round the date like this? or only sysdate can be rounded or if the date is stored in a variable can only be rounded
select round('21-feb-1999','mon') from dual;
ADVERTISEMENT
PL/SQL :: Function To Round To Previous Quarter
Feb 28, 2013I need to have a function which will round to previous quarter
For ex :
if the value is 1.95 it should give 1.75
if the value is 1.60 it should give 1.50
PL/SQL :: Round Function Output Depend On 2nd Digit After Decimal Point
Oct 19, 2012I have a question on Round Function
Round(###.###,1)
Will the round function output depend on 2nd digit after decimal point also or not?
Application Express :: Unable To Set Default Date Value For Tabular Form DB Date Field?
Dec 3, 2012Version : 4.1.1, I have a tabular form on a DB table. One of the columns is a date field. When the user hits the "add Row" button on the tabular form, I want the Date field to be defaulted to sysdate. Here is what I have tried so far,
1. Created a "hidden" item P1_SYSDATE and populated the default value with sysdate. After this, under the DB tabular report date field, I used default type - Item/application on this page and entered P1_sYSDATE
2. Instead of populating the default value of the P1_SYSDATE hidden item, I created a before regions process and added
:P1_SYSDATE := sysdate
and added P1_SYSDATE to default type of the tabular date field with default type as "ITem/application on this page.
I get the error
ORA-01790: expression must have same datatype as corresponding expression
I tried to_Char(sysdate,'dd-mon-yyyy') and then converting it back to to_date. still no luck.
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 RelatedPL/SQL :: Round The Number To Nearest 10k
Nov 1, 2012I am facing small issue, i need to round the number to the nearest 10k for some finance reporting
ex
1. if number is 2250 round to 10,000
2. if number is 5500 round to 10,000
3. if number is 4912345 round to 4920000
etc..
the number should always round up to nearest 10k..
I am using round function to achieve this..
case 1:-
SQL> Select round(2250,-4) From dual;
ROUND(2250,-4)
--------------
0
I need 10,000 here..
case 2:-
SQL> Select round(5500 ,-4) From dual;
ROUND(5500,-4)
--------------
10000
SQL>
This is working fine...
case 3:-
SQL> Select round(4912345 ,-4) From dual;
ROUND(4912345,-4)
-----------------
4910000
This should round UP to 4920000
let me know if there is any way i can achieve this...
SQL & PL/SQL :: Round Robin Implementation Through Script?
Aug 19, 2010 I have to implement round robin algorothim in sql script for allocation of some items to another entity. Not really sure how to do that and if there would be locks while selecting the one which is already being updated and so...!
how to go about it when the number of users involved would be very high to whom the items would be allocated in round robin function.
PL/SQL :: Function Date
Aug 30, 2012CREATE OR REPLACE FUNCTION my_to_date (value_in IN VARCHAR2)
RETURN DATE
IS
TYPE mask_t IS TABLE OF VARCHAR2 (30)INDEX BY BINARY_INTEGER;
fmts mask_t;
[Code]....
When i try using this method i am getting error...
SQL> select my_to_date(to_char(07/11/1987),'yyyy') from dual;
select my_to_date(to_char(07/11/1987),'yyyy') from dual
*
ERROR at line 1:ORA-06553: PLS-306: wrong number or types of arguments in call to 'MY_TO_DATE'
what is the error here...What is the solution for this function.....how to execute this function
When i am using ananymous block i am getting this answer:
SQL> declare
2 v date := '07-nov-1987';
3 u date;
4 begin
[Code]...
PL/SQL procedure successfully completed.
SQL & PL/SQL :: Date Function?
Nov 22, 2011i have one table chequedetails in this 4 rows are there that is
date name sno
1-10-2011 b 1
10-10-2011 c 2
25-10-2011 d 3
11-11-2011 e 4
these records are there in my table in this i selected between date it wil display o/p like this
date name sno
1-10-2011 b 1
11-11-2011 e 2
my query
select * from chequedetails where date between '1-10-2011' and '11-11-2011'
SQL & PL/SQL :: Unable To Load Date Column Into CSV
May 10, 2012I am using this procedure to load oracle data into csv file.
/* Formatted on 2012/05/10 17:18 (Formatter Plus v4.8.8) */
CREATE OR REPLACE PROCEDURE generate_order_csv (
p_dir IN VARCHAR2,
p_file_name IN VARCHAR2,
p_data_file IN VARCHAR2 := NULL
)
[Code]...
-- This part outputs the HEADER
v_fh := UTL_FILE.fopen (UPPER (p_dir), p_file_name, 'w', 32767);
FOR j IN 1 .. col_cnt
LOOP
v_finaltxt :=
LTRIM (v_finaltxt || '|' || LOWER (rec_tab (j).col_name), '|');
[Code]...
.
-- This part outputs the DATA
IF NOT v_samefile
THEN
v_fh := UTL_FILE.fopen (UPPER (p_dir), p_data_file, 'w', 32767);
END IF;
[Code]..
When I execute this procedire I am facing two columns.
1) First column ORDER DATE is not laoding into .csv but when I execute query from SQL, I am able to see the date column values.
2) There is an overlapping of the result string. Last column is coming up into second row.
what changes to be done?
Performance Tuning :: Px Send Round-robin?
Jun 19, 2012what is "px send round-robin "
View 1 Replies View RelatedSQL & PL/SQL :: Date Comparisons In A Function
Dec 8, 2010I am passing a date parameter to a function. Based on parameter dates comparison the function return me few names.Unfortunately, i am not getting all the names i should.
I suspect this is because the date comparisons are not working properly. Perhaps becausee of the date format. Both paramter and cursor date values come from column type date.Some values are in this format 8/11/2004 and few others in this 7/11/2005 4:40:00 PM..
find_names(p.prospect_id, pro.proposal_id, pro.start_date, pro.stop_date )
CREATE OR REPLACE FUNCTION find_names(p_prospect_id VARCHAR2
,p_proposal_id VARCHAR2
,p_start_dt date
,p_stop_dt date)
RETURN VARCHAR2 IS
v_result VARCHAR2(1000);
v_name VARCHAR2(200);
[code]...
PL/SQL :: Oracle Date Function
Jun 16, 2013I have a table with date column (16/06/1996 15:03:59) as value in the displayed format. As I have tried with the below query format I could not able to retrieve the
records. select * from <table> where DATE_INSERT=SYSDATE;
Retrieve the rows for the tables ?
SQL Net Round Trips On Long Data Type Table
Oct 12, 2012We are working on a performance tuning aspect, where in a table has a LONG datatype and number of round trips are increasing based on the number of rows fetched.i.e one round trip for every row.
Background.
1. created a table with LONG data type.
2. inserted bulk load of data.
3. set auto trace on and executed below tests..
SQL> select count(*) from test_long;
COUNT(*)
----------
6110
SQL> desc test_long
Name Null? Type
----------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
ID NUMBER
TESTLONG LONG
[code].....
but on LONG column, irrespective of the array size the round trips does not reduce.
SQL & PL/SQL :: Fetch System Date Using Function?
Oct 18, 2010create or replace function get_date(nn date)
return date as age date;
BEGIN
age := sysdate;
END;
i want to return that value in front end using callable statament..
Forms :: FRM-40513 / Unable To Get Date / Time From Database
Sep 25, 2013after spent a lot of time surfing the web looking for this error frm-40513 i did not found any answer about this, So I've removed the date items on my canvas and ran perfectly... after that i opened another copy of the form. In one item there was a initial value $$DBDATETIME$$ .. so that was the error.. i removed that value and i ran my form 11g (weblogic) again and the error has gone.
View 6 Replies View RelatedSQL & PL/SQL :: Time Calculation - Make Varchar To Timestamps But Caught With Round Up / Down?
Apr 12, 2011 calculating values of A & B
(both these fields a_std and a_time are coming as varchar from the parent table in a cursor.(basically they are time period and actual arrival time respectively)
i was juggling with the attempt to make varchar to timestamp or date..but caught with Round up /Round down)
Formula ->
A = Round down [A_TIME - A_STD]
B = Round up [A_TIME) - 10 minute + A_STD]
where
A_TIME VARCHAR2(8) N Time (Format" HH:MM AM/PM") eg "3:50 PM"
A_STD VARCHAR2(5) N Standard time (Format" HH:MM") eg "1:00"
Allowed values for A & B after round up/down = multiple of 10 ( 11:00,11:10,11:20 etc.)
SQL & PL/SQL :: Date Error In Execute Immediate Query Through Function?
Jun 25, 2012I've used a date in execute immediate query in function, but at the time passing the date as input parameter and getting the result i'm getting following error.
CREATE TABLE MIS.TEMP
(
ID NUMBER(8),
STOCKDATE DATE,
STOCKQTY NUMBER(10,2)
);
[code]....
SQL> select getstockqty(1,to_date('31/03/2012','dd/mm/yyyy')) from dual;
select getstockqty(1,to_date('31/03/2012','dd/mm/yyyy')) from dual
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at "MIS.GETSTOCKQTY", line 11
SQL & PL/SQL :: Function Invoked By Passing Date Parameter
Oct 18, 2011get rid of the below error
CREATE OR REPLACE FUNCTION fn (
p_salesrep_id IN jtf_rs_salesreps.salesrep_id%TYPE,
p_org_id IN jtf_rs_salesreps.org_id%TYPE,
p_cnf_date IN emcint_ord_headers_all.creation_date%TYPE
[Code]....
Invoking Functions
select fn(-3,293,'1/1/1952'), resource_id from jtf_rs_salesreps
where rownum < 5
ORA-06552: PL/SQL: Statement ignored
ORA-06553: PLS-382: expression is of wrong type
Forms :: Unable To Get Records While Queried Through Date Field In Oracle
Oct 16, 2012In a data block(it lists all the records created), I tried to search for the records which are created on a particular date. So I entered the date in query mode in that data block. But it is not returning any rows. But if I searched through other fields in that data block except that date data type it is returning the appropriate records.
View 10 Replies View RelatedSQL & PL/SQL :: Function To Find Max Last Logout Date For Each Agent Daily
Jun 3, 2010I have to create a function. I need to find the max last logout date for each agent daily. For example, if an agent logged in for the first time at 9:00 and he logged out at 12:00 and he logged in again in 14:00 and he logged out at 15:00 the time I need my report to show is 15:00. How can I do that?In order to make it easiest for you to understand I am sending you this query:
select
a.login as login2,
To_Char(max(s.endtime), 'dd/MM/yyyy, HH24:MI:SS') as lastLogout
from cti.agent a
inner join cti.agentsessionlog s
on s.agentid = a.agentid and To_Char(s.endtime) != '31-DEC-99 11.59.59.000000 PM'
group by a.login;
This query returns the agent's login and the agent's last logout time. It works fine if I enter a date between but I cannot do that. If a use this query as it is and I try to export a report for 31/5 it shows as lastlogout the logout for 01/06 or 2/06. Is there a function I can use? I have a deadline.
SQL & PL/SQL :: Display Date Ranges In One Column As Separate Date Periods (start And End Date) In Two?
Jun 1, 2010I'm trying to work out how to take a table like this:
IDDate
12502-Feb-07
12516-Mar-07
12523-May-07
12524-May-07
12525-May-07
33302-Jan-09
33303-Jan-09
33304-Jan-09
33317-Mar-09
And display the data like this:
IDPeriodPeriod StartPeriod End
125102-Feb-0702-Feb-07
125216-Mar-0716-Mar-07
125323-May-0725-May-07
333102-Jan-0904-Jan-09
333217-Mar-0917-Mar-09
As you can see, it's split the entries into date ranges. If there is a 'lone' date, the 'period start' and the 'period end' are the same date.
Forms :: Expiry Date To Automatically Show A Date 15 Years After Initial Date
Apr 12, 2010I have a two date fields in my form; valid from date and expiry date.
Currently my valid from date has an inital value property of $$date$$ which automaitcally brings up todays date.
I need my expiry date to automatically show a date 15 years after this date?
PL/SQL :: To Create Function Based Index For Group Function Columns
Jun 15, 2012Is anyway to create function based index for group function columns.
For example
select max(timestamp),min(age),averge(sal).... ... .. from tab;
PL/SQL :: Calling External C Function / ORA-06521 Error Mapping Function
Feb 4, 2013I have the following C code:
class Factorial {
public:
int getVal (int a);
};
[code]....
/When I am trying to execute this function always get the ORA-06521. I changed the data types - but nothing changed.
Just in case, listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = db)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
[code]....
SQL & PL/SQL :: Difference Between Stand Alone Function And Function Declared In A Package?
Mar 11, 2010What is the Difference between a Stand Alone Function/Procedure & a Function/Procedure declared in a Package.
View 2 Replies View RelatedSQL & PL/SQL :: What Is Advantage Of Deterministic Function Over Normal Function
Jun 10, 2010What is advantage of Deterministic function over normal function?
What is the diff B/W Deterministic function and normal function and also give me a example in which scenario we use Deterministic function?
Visual Studio :: Set Query Window Default Date Format To Full Date?
Oct 24, 2013When I run a query form the the Query Window in Visuial Studios 2012 all the date fields truncated to 'mm/dd/yyyy', but i need the full date returned. I am able to get full date from TO_char(MyDateField, 'yyyy-mm-dd hh24:mi:ss'), but if I do TO_DATE(MyDateField, 'yyyy-mm-dd hh24:mi:ss') it only returns 'mm/dd/yyyy'. I'm sure this is a simple setting in Visual studios but I cant find it to save my life. Is there there a way to have the full date returned by default?
View 0 Replies View RelatedDate Arithmetic - Formula To Subtract Years And Give Date
Sep 27, 2007I am a novice in oracle
I have 2 columns in my table
->Col1 with experience in years entered as an integer
->Col2 with current date
I need to add another column as a date value adn for that i need to subtract Currentdate-Col1 when i tried currentdate-Col1 it just subtracted the days i need the formula to subtract years and give a date
I have worked in DB2 and all u need to do there was add the keyword years at the end but in oracle the same does not work
Server Utilities :: How To Get Date And Time In Date Column While Sqlldr
Jun 6, 2012I am not able to load complete date along with time in the date column. here is my table desc
DESC STAGE
Name Null Type
----------------------------------
TABLE_NAME NOT NULL VARHCAR(20)
RECORDCOUNT NUMBER
CREATED_DATE NOT NULL DATE
my control file is like this
LOAD DATA
APPEND
INTO TABLE SCOOP.STAGE
FIELDS TERMINATED BY ","
( TABLE_NAME
,RECORDCOUNT
,CREATED_DATE DATE(16) "YYYYMMDDHH:Mi:SS"
)
the data gets loaded, but it appears like this in the table
HIGHSCHOOL3080606-JUN-12
MIDDLESCHOOL8768006-JUN-12
BUT I WANT COMPLETE DATE AND TIME (HH:MI:SS) , HOW CAN I GET IT (THIS IS HOW I WANT 06-JUN-12 11:07:33)