REPLACE PROCEDURE ABC (IN in_Period_start CHAR(10),IN in_Period_end CHAR(10))
In the input parameter i am giving (Jan2012,Dec2012).
I have to find out different data between the range jan 2012 to dec 2012 in month wise.But in my table structure the data is like below...
and so on....
Then how i will make the comparision to get the data between that specified range?
You need to convert your input parameters in DATE format in your procedure and then probably you can use EXTRACT function to get the month and year out of it and compare if with your table.
Pass start and end date in the stored procedure parameters and you can construct the date from the integer values in your table. When both the parameters are in same format then the comparison is just a piece of cake...
This piece of code might help you
REPLACE PROCEDURE SP_Comparison (IN in_Period_start_dt DATE,IN in_Period_end_dt DATE)
CREATE TABLE T2 AS
WHERE cast((_year - 1900) * 10000 + (_month * 100) + _day as date) BETWEEN in_Period_start_dt and in_Period_end_dt;
) WITH DATA;
Or use the following query to convert your input parameters to year and month
select extract(year from cast('Jan2012' as date format 'mmmyyyy')) as ur_year
,extract(month from cast('Jan2012' as date format 'mmmyyyy')) as ur_month;
and then compare it with the data present in your table.
In the stored procedure cast the input parameters as Date e.g.
CAST(in_Period_Start as DATE format 'MMMYYYY')
This will convert Jan2012 into date format 1/1/2012