SQL & PL/SQL :: Date Comparisons In A Function

Dec 8, 2010

I 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]...

View 8 Replies


ADVERTISEMENT

Table Data Comparisons Across Two Databases?

Oct 4, 2011

I want to compare the table contents across the two different databases , one is on netezza and other is on Exadata.

View 2 Replies View Related

PL/SQL :: Function Date

Aug 30, 2012

CREATE 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.

View 7 Replies View Related

SQL & PL/SQL :: Date Function?

Nov 22, 2011

i 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'

View 22 Replies View Related

PL/SQL :: Oracle Date Function

Jun 16, 2013

I 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 ? 

View 2 Replies View Related

SQL & PL/SQL :: Fetch System Date Using Function?

Oct 18, 2010

create 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..

View 2 Replies View Related

PL/SQL :: Unable To Round Date Function?

Feb 21, 2013

cant 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;

View 2 Replies View Related

SQL & PL/SQL :: Date Error In Execute Immediate Query Through Function?

Jun 25, 2012

I'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

View 12 Replies View Related

SQL & PL/SQL :: Function Invoked By Passing Date Parameter

Oct 18, 2011

get 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

View 8 Replies View Related

SQL & PL/SQL :: Function To Find Max Last Logout Date For Each Agent Daily

Jun 3, 2010

I 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.

View 7 Replies View Related

SQL & PL/SQL :: Display Date Ranges In One Column As Separate Date Periods (start And End Date) In Two?

Jun 1, 2010

I'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.

View 13 Replies View Related

Forms :: Expiry Date To Automatically Show A Date 15 Years After Initial Date

Apr 12, 2010

I 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?

View 8 Replies View Related

PL/SQL :: To Create Function Based Index For Group Function Columns

Jun 15, 2012

Is anyway to create function based index for group function columns.

For example

select max(timestamp),min(age),averge(sal).... ... .. from tab;

View 5 Replies View Related

PL/SQL :: Calling External C Function / ORA-06521 Error Mapping Function

Feb 4, 2013

I 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]....

View 6 Replies View Related

SQL & PL/SQL :: Difference Between Stand Alone Function And Function Declared In A Package?

Mar 11, 2010

What is the Difference between a Stand Alone Function/Procedure & a Function/Procedure declared in a Package.

View 2 Replies View Related

SQL & PL/SQL :: What Is Advantage Of Deterministic Function Over Normal Function

Jun 10, 2010

What 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?

View 4 Replies View Related

Application Express :: Unable To Set Default Date Value For Tabular Form DB Date Field?

Dec 3, 2012

Version : 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.

View 1 Replies View Related

Visual Studio :: Set Query Window Default Date Format To Full Date?

Oct 24, 2013

When 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 Related

Date Arithmetic - Formula To Subtract Years And Give Date

Sep 27, 2007

I 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

View 8 Replies View Related

Server Utilities :: How To Get Date And Time In Date Column While Sqlldr

Jun 6, 2012

I 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)

View 10 Replies View Related

PL/SQL :: Retrieve Date Range From 2 Search Input Date Fields

Aug 16, 2012

I would like to inquire how do I code my select statement if my user enter a search date range(search startDt: 01/08/2012 and search endDt :30/09/2012) and I will like to retrieve only the 7records out from my table as shown below ?

STARTDT_     ENDDT_
01/08/2012 01/08/2012
01/06/2012 31/12/2012
01/08/2012 01/08/2012
01/09/2012 01/09/2012
01/09/2012 31/12/2012
01/07/2012 01/07/2012->not retrieved out
01/08/2012 01/08/2012
01/01/2012 31/12/2012

View 6 Replies View Related

SQL & PL/SQL :: Declare Date Variable And Assign It To Current System Date

Apr 19, 2011

Write a program to declare a date variable and assign it to the current system date. Depending on the day of the month the program should print the following:

If day is 1-10 then print "It is day<day number> of <month name>. It is early in the month".
If day is 11-20 then print "It is day<day number> of <month name>. It is the middle of the month".
If day is 21-31 then print "It is day<day number> of <month name>. It is nearly the end of the month".

For example, if the day is November 30, then print "It is day 30 of November. It is nearly the end of the month".

View 9 Replies View Related

Can Partition A Table Based On Date If It Does Not Have A Date Column

Jun 21, 2012

How can we partition a table based on date if it does not have a date column.

Actually I have to compare two tables on daily basis and fetch few rows from those two tables and enter it to a third table.But both these tables does not have a date column.

I am confused if i need to alter those tables and add date column or if there is some way in which i can compare the data from the two tables for that particular day only and not the whole table data.

View 1 Replies View Related

Converting Date Field To Datestamp (Epoch Date)

Apr 11, 2013

I have a small prolem thats best described like this....

a table called TONY with a field named VISITED (date as YYYYMMDD).

We want to populate the field TIMESTAMP (Last visited timestamp, 18 digits) using midnight or 00:00:00 on VISITED value.

Something like:

UPDATE TONY SET TIMESTAMP = �(whatever the formula is involving VISITED).

but i cannot figure out the best way to derive the TIMESTAMP value...

it's a date to epoch conversion, and i can find many examples of Epoch to date, but thats the wrong way around for me i'm afraid!

Oracle 11gR2 by the way...

View 4 Replies View Related

SQL & PL/SQL :: Converting Char Into Date With Column Having Non-date Formats

Aug 16, 2010

i have a table with the following description

create table gl_periods(period_name varchar2(10),transactions number (2) );

with the data as :

period_name transactions
------------ --------------
JAN-10 12
FEB-10 12
MAR-10 8
APR-10 23
ADJ_TOM-10 25
MAY-10 37
JUN-10 41
JUL-10 10
PHY_JAY-10 6
AUG-10 14
SEP-10 22

My requirment is to find out the period names and transactions which are in valid date formats and are less than sysdate and the non date formats are adjustments made by different users for their transactions

View 8 Replies View Related

Server Utilities :: Loading Date Value Into DATE Column?

Oct 18, 2012

I want to load data from a file using sqlldr.I have a table commissions
(
technician_id char(5)
, tech_name char(30)
, Comm_rcd_date DATE
, Comm_Paid_date DATE
, comm_amt number(10,2)
)

my file is
00001,TIMOTHY TROENDLY,2011-03-04T01:45:12+0006,2011-03-04T01:45:12+0007,123.56
00002,KENNETH KLEMENZ,2011-03-04T01:45:12+0006,2011-03-04T01:45:12+0009,123.56
00003,SHUNDAR ARDERY,2011-03-04T01:45:12+0006,2011-03-04T01:45:12+0005,123.56
write a ctl file to load this data.

View 6 Replies View Related

SQL & PL/SQL :: Required Data For Month To Date And Year To Date?

Apr 30, 2012

I want to get data for month to date. For example, If I pass today or any day date as parameter then i should get data for that month(month of passing date) up to passing(parameter) date. As well as i have to get year to date.For example, If I pass today or any day date as parameter then i should get data for that financial year(year of passing date) up to passing(parameter) date. how to get month to date and year to date data.

View 3 Replies View Related

Forms :: Date Field Gets Disabled When Date Format Is Given?

Oct 3, 2012

A field named xxx_date is a text item which we have to enter manually so as to update a record in that particular date. This is a mandatory field without which we cannot continue the data entry..

I am getting this error while trying to update the record

FRM-40509 :Oracle error :unable to update record

I have kept the enabled = yes
required=no
data type=Date.. in the property pallet

View 2 Replies View Related

PL/SQL :: Reset Date In Specific Format (Date / Time)

Dec 23, 2012

I want to reset my date to this format: 12/31/2012 11:59:59 PM - see code below:

DECLARE
v_latest_close DATE;
BEGIN
v_latest_close := TO_DATE ('12/31/2012 23:59:59 ','MM/DD/YYYY HH24:MI:SS');
DBMS_OUTPUT.PUT_LINE('The new date format is : '|| v_latest_close);
END;

the code above displays only : 12/31/2012 instead of 12/31/2012 11:59:59 PM

View 4 Replies View Related

PL/SQL :: Date Difference - Include Both Start And End Date In Range

Aug 8, 2013

select to_date('28-FEB-2013') - TO_DATE('01-FEB-2013')  FROM DUAL 

gives me 27 days, what should I do to get 28 ? meaning include both the start and end dates in the range ?

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved