SQL & PL/SQL :: Parameter For Date - Quarter Year
Jan 26, 2013
IF THE USER ENTER parameter for date(yymm) in a report as 201301
select vndr#,sum(net_sales_value) from mnthly_sales_value where vndr# = 111 and yymm = :yymm group by vndr#;
but I need result of 3 months, but in my table data is stored for one month so how to get 3 months then again 3 months so on for Q1,Q2,Q3,Q4
yymm between 201301 and 201303 and
yymm between 201304 and 201306 and
yymm between 201307 and 201309 and
yymm between 201310 and 201312
in the same query
what i have to do for this
View 3 Replies
ADVERTISEMENT
Oct 5, 2010
I have year/quarter number field (200903 3-rd quarters of 2009) and I need to convert to data format.
View 5 Replies
View Related
Nov 11, 2013
My problem is I need to get the data for every quarter for financial year and also I need data for every week for financial year.For example for financial year 2012-13, Apr2012 to Jun2012 would be Q1, Jul2012 to Sep2012 would be Q2 and so on. Total 8quarters should come upto Apr2013.In the same way 1st apr 2012 to 7th apr 2012 would be week1, 8th apr to 15th apr would be week2 and son on. How to write a query for this scenario in oracle.
View 4 Replies
View Related
Jul 16, 2012
I need to display quarter of current year in select list.
for example current year is 2012 so, i want Jan-Mar 2012,Apr-Jun 2012,July-Sept 2012,Oct-Dec 2012 in select list.
View 3 Replies
View Related
Jan 25, 2013
My below query is working fine with sysdate but when m passing a date it's throwing an error to get the current qtr end date..
select add_months(trunc('20-dec-2012','q'),3) - 1 from dualERROR
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected DATE got NUMBER
even after adding the to_date function between trunc and date.
View 5 Replies
View Related
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
Mar 20, 2008
I have a procedure that has a 'INTERVAL YEAR TO MONTH' parameter. What value do I pass to this parameter?
View 1 Replies
View Related
Apr 26, 2013
I am populating a time dimension table. One job is to assign business day with sequence number on monthly basis and by year. Business day does not include weekends and all federal holidaies.
I created a PL/SQL block to handle this job. It works. However, I haven't figure out how to pass the month number and year number into PL/SQL block automatically. Now I have to manually enter year and month number (on Toad) to pass to block to make it work. If I create it as stored procedure and write another block to call it, I can pass year and month number into parameter there like this:
exec my_sp_name ('1', '2013');
But all of these are not good enough. I want to use code to automatically pass yesr and month number into PL/SQL block. So that I can make it better. . here is the PL/SQL block I created.
declare i number := 1;
begin
for r in (select * from time_dim where calendar_month_number = &month and calendar_year= &year and business_day_flag = 'B' order by date_sk_id)
loop
[Code]....
View 11 Replies
View Related
Sep 27, 2010
I got a table with a date-typed column called: "Birth_date", and I wanna write a function that retrieves all the records that "got a birthday" this week.
in order to check weather a record got a birthday this week I need to check only the day & month of "Birth_date" that BETWEEN (sysdate-7) AND (sysdate),
but I don't know how..
View 39 Replies
View Related
Mar 1, 2012
I have a sql like this
select inv_dtime where inv_dtime between trunc(sysdate,'YYYY') and last_day(trunc(sysdate,'YYYY')) from temp;
I want to have the start date of the year and end date of the year in my condition. like between 01-JAN-2012 AND 31-DEC-2012. I tried the above but it doesn't come.
View 4 Replies
View Related
Apr 5, 2011
How to get a date using Year (say 2009), Week (35) and Weekday (5). I have read only permission to database, so I can not create a function.
View 8 Replies
View Related
Oct 18, 2010
I have a table and below are the rows:
create table employee_rev(employee_id number, month_name varchar2(10), month_end date, financial_year date, revenue_amt number)
Begin
insert into employee_rev values(111, 'DEC-09', '31-DEC-2009', '01-APR-2009', 1300);
insert into employee_rev values(111, 'JAN-10', '31-JAN-2010', '01-APR-2009', 1000);
insert into employee_rev values(111, 'FEB-10', '28-FEB-2010', '01-APR-2009', 800);
insert into employee_rev values(111, 'MAR-10', '31-MAR-2010', '01-APR-2009', 1000);
insert into employee_rev values(111, 'APR-10', '30-APR-2010', '01-APR-2010', 1000);
insert into employee_rev values(111, 'MAY-10', '31-MAY-2010', '01-APR-2010', 1100);
insert into employee_rev values(111, 'JUN-10', '30-JUN-2010', '01-APR-2010', 2100);
End;
I also need a YTD (Year to Date) field at the last which is sum of current month revenue_amt and sum(revenue_amt) for previous months for a particular financial_year_date.
The output should be:
Person_ID Month End Date Year Date Rev YTD
111 Dec-09 31-Dec-09 1-Apr-09 1300 1300
111 Jan-10 31-Jan-10 1-Apr-09 1000 2300
111 Feb-10 28-Feb-10 1-Apr-09 800 3100
111 Mar-10 31-Mar-10 1-Apr-09 1000 4100
111 Apr-10 30-Apr-10 1-Apr-10 1000 1000--change in financial year
111 May-10 31-May-10 1-Apr-10 1100 2100
111 Jun-10 30-Jun-10 1-Apr-10 2100 4200
I guess this should be achievable using some analytical functions, but I am unable to get the desired output.
View 7 Replies
View Related
Apr 27, 2011
I have a table as below:
Year Month Value
2011 01 15
2011 02 26
2011 03 34
[code]...
The value is an aggregate Year to Date Figure And I was wondering what the best method of splitting this data out into a Monthly Figure so that it would look like below:
Year Month Mth Value
2011 01 15
2011 02 11
2011 03 8
2011 04 9
[code]...
View 9 Replies
View Related
Jun 4, 2010
I've done this once before, but can't seem to find the sql.
How can I sort by month and year on a column called ex: TEST_dATE
JAN 2007
FEB 2007
APR 2008
SEP 2009
OCT 2009
FEB 2010
JUN 2010
View 15 Replies
View Related
Jul 8, 2010
i want to find out difference between two dates in day-month-year. How can i do it?
For example how many days,months are years are between '01-jul-1979' and '08-jul-2010'
View 4 Replies
View Related
Nov 12, 2012
basically I have to insert in a date field a string that is a year(four characters).
The problem is that I have to convert a string in date obtaining just a year. How can i do this?
View 14 Replies
View Related
Oct 8, 2012
select b.penjara_id, p.penj_lokasi, a.no_daftar, b.episod, b.nama1,to_char(a.trkh_mula_prl,'dd/mm/yyyy') as trkh_mula_prl, to_char(bulan_proses,'mm/yyyy') as bulan_proses,
b.epd, b.lpd
from prl_daftar_proses a, senarai_pesalah b, penjara p
where a.no_daftar=b.no_daftar
and a.episod=b.episod
and b.penjara_id = p.penjara_id
and a.setuju_jplp is null
and a.bulan_proses between to_date(:FROM,'dd/mm/yyyy') and to_date(:TO,'dd/mm/yyyy')
order by b.penjara_id, a.bulan_proses,a.no_daftarHi,
how i can insert only month and year from the value that have full date in the database?
for example,the date is 09/18/2012, but i just want to insert 09/2012 as parameter. If i want to insert only one parameter, i can do that..But i have problem when I want to insert two parameters..
View 16 Replies
View Related
Dec 26, 2012
Getting problem with below query.
select to_date('30-DEC-00','DD-MON-YYYY') from dual
Error starting at line 1 in command:
select to_date('30-DEC-00','DD-MON-YYYY') from dual
Error report:
SQL Error: ORA-01841: (full) year must be between -4713 and +9999, and not be 0 01841. 00000 - "(full) year must be between -4713 and +9999, and not be 0"
*Cause: Illegal year entered
*Action: Input year in the specified range
View 1 Replies
View Related
Sep 20, 2011
i have a requirement to create a query which gets the same month from the previous year of the selected date .
Example : i select SEP-2011 ( as sysdate )
it will return the SEP-2010 as well .
here is the code which works fine with sysdate, dual :
SELECT
TO_CHAR(ADD_MONTHS(SYSDATE,-12),'MON-YYYY') TMLY, TO_CHAR(SYSDATE,'MON-YYYY') TMTY FROM DUAL;
in my schema table Ive got a filed named PERIOD_NAME (varchar2) , which holds date values in ( Mon-YYYY ) format (e.g Sep-2011)
am unable to apply the above code on it , i guess its the data type pf the field .
View 5 Replies
View Related
Feb 3, 2011
I given the table name,column name,datatype and sample record in the table. I have given the sample record for 01-jan-2008 to 8-Jan-2008, but in the real thing it will be for 30 years.
My Requirement:
For each class_no (202,203..), I need the missing date excluding weekends (sat, sun), I have provided the sample output below.
Table Name : ABC
Column Name : Class_no Data Type : Number
Column Name : Class_DateData Type : Date
Sample Record in the Table :
Class_noClass_Date
202 1-Jan-08
202 2-Jan-08
202 7-Jan-08
202 8-Jan-08
203 1-Jan-08
203 2-Jan-08
203 3-Jan-08
203 7-Jan-08
203 8-Jan-08
OUTPUT:
Class_noClass_Date
202 3-Jan-08
202 4-Jan-08
203 4-Jan-08
View 5 Replies
View Related
Jun 22, 2010
In a procedure i need to pass date parameter, to check how to do this. I create a simple procedure, but it giving
PLS-00049: Bad Bind Variable 'FD'
PLS-00049: Bad Bind Variable 'TD'
How to resolve .......
CREATE PROCEDURE pass_date_parameter
AS
fd VARCHAR2 (10) DEFAULT '01-01-2010';
td VARCHAR2 (10) DEFAULT '01-06-2010';
ffd VARCHAR2 (10) DEFAULT '01-01-2010';
ttd VARCHAR2 (10) DEFAULT '01-06-2010';
[Code]....
View 19 Replies
View Related
Nov 20, 2010
i need to get first Tuesday per each month between two parameter i will use cursor in forms.
for example
my parameter is '04122007' and '31122009'
i need the first tuesday for each month
like '02122008'
'01�12009'
'03�22009' ext....
View 8 Replies
View Related
May 13, 2013
How to use date as an input parameter,im supposed to use varchar2 as the data type
CREATE OR REPLACE PROCEDURE mail1 ( recievers VARCHAR2 ,p_date in varchar2 )
IS
sender VARCHAR2(30) := 'xyz@gmail.com';
mailhost VARCHAR2(100) := 'host address';
TAB VARCHAR(2) := CHR(9);
mail_conn utl_smtp.connection;
[code].....
cursor c1 is
--select activity_date,procedure_name,status_message,error_desc from staging_activity_log where rownum between 1 and 10 ;
select activity_date,procedure_name,status_message,error_desc from staging_activity_log where error_desc is not null and trunc(activity_date) >= to_date(p_date,'DD-MON-YYYY') ;
BEGIN
[code].....
when i execute i get
BEGIN mail1 ('xxx@gmail.com,yyy@gmail.com,'28-jan-2008'); END;
Error at line 1
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "MAIL1", line 27
ORA-06512: at line 1
View 3 Replies
View Related
Aug 5, 2013
i have below requirement in one program date is input parameter. but that should work whatever date format like DD/mon/yyyyyyyy.mm.dd what shd i do input parameter from dateand to date
View 5 Replies
View Related
Mar 26, 2012
I have need to know the best (least expensive) way to create a stored procedure that creates a new records in a table using a sequence and return the primary key (sequence value) for this inserted record:
CREATE TABLE TEST_A (SERIAL NUMBER PRIMARY KEY, NAME VARCHAR2(20));
CREATE SEQUENCE SEQ_TESTA_PK START WITH 1
NOCACHE
NOCYCLE;
CREATE OR REPLACE TRIGGER TRG_TESTA_PK
BEFORE INSERT ON TEST_A
[code]....
View 8 Replies
View Related
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
Dec 12, 2012
i have the below query
select to_char(report_date, 'YYYY MM Mon'), count(1) no_of_times
from (
select to_date('&&YYYYMMDD', 'YYYYMMDD')+rownum report_date
, mod(rownum,14) mod_result
from all_objects
[code]...
need to convert as procedure based on input date parameter.I will pass the input date from java environment and need to see the sql query output in front end.
View 7 Replies
View Related
Mar 28, 2012
I have created a stored procedure that checks if a file exists and gets a date from the file if it exists. The date is then used as a paramter. See below:
CODEcreate or replace
PROCEDURE "P_Load_Stamp" AS
v_exists BOOLEAN;
v_length NUMBER;
v_blocksize NUMBER;
[code]...
The above codes works perfectly and I scheduled it using SQLPLUS as follows:
CODEvariable jobno number;
variable instno number;
begin
select instance_number into :instno from v$instance;
[code]...
My problem is that I need to pass the date from the above procedure as a parameter to another stored procedure. So I modified my code as follows (the parts in red):
CODEcreate or replace
PROCEDURE "P_Load_Stamp" (vCTIDATE OUT varchar2) AS
v_exists BOOLEAN;
v_length NUMBER;
[code]...
Now it doesn't strike me as a rights issue since I created it in the schem schema. What could I be doing wrong here?
View 1 Replies
View Related
Aug 19, 2013
steps to achieve this report I need a Date Column on top of the page Depending on the selected date, Result should be displayed below for e.g.
suppose i select a date as 14-aug-2013
Enter Report Date ___________________ ( Date Picker) Location Name Report Date Quantity valueXYS 14/08/2013 5000 1200000ABCD 14/08/2013 2000 24000-------------------------------------------------------------------------------------------------TOTAL 7000 1224000
View 9 Replies
View Related
Jul 14, 2011
this is how do I calculate end of quarter ignoring days off.
with
calendar1 as
(select * from
(select to_date('29.03.2012','DD.MM.YYYY') as arcdate, 0 as dayoff from dual union all
select to_date('30.03.2012','DD.MM.YYYY') as arcdate, 0 as dayoff from dual union all
[code]...
I need to create list of end_of_quarter for period "between sysdate MINUS two years and sysdate PLUS two years". dayoff is not only Saturday and Sunday, but it's also state holiday, etc. In real life table calendar1 is updated by central bank.
View 4 Replies
View Related
