CODEWITHSUNDEEP
mysqlcursor
nakul
nakul

Reputation: 533

cursor does not show date value in mysql procedure

DECLARE no_more_rows BOOLEAN;
DECLARE loop_cntr INT DEFAULT 0;
DECLARE num_rows INT DEFAULT 0;
DECLARE nrl_id INT DEFAULT 0;
DECLARE tap_pro_id INT DEFAULT 0;
DECLARE agr_date Date;
DECLARE tap_proposed_start_date Date;
DECLARE tap_proposed_end_date Date;
DECLARE tap_rent_type varchar(20);
DECLARE tap_creation_ts TIMESTAMP;
DECLARE tap_previous_date TIMESTAMP;
DECLARE tap_usr_id INT DEFAULT 0;
DECLARE tap_com_id INT DEFAULT 0;
DECLARE lease_fire_date TIMESTAMP;
DECLARE increment_fire_date TIMESTAMP;
DECLARE increment_next_fire_date TIMESTAMP;

DEClARE lease_cursor CURSOR FOR 
select
    ta.TAP_PROPERTY_ID,
    ta.TAP_PROPOSED_START_DATE,
    ta.TAP_PROPOSED_END_DATE,
    ta.TAP_RENT_TYPE,
    ad.AGR_CREATION_TS,
    ad.AGR_CREATED_BY,
    apd.APP_COM_ID
FROM agreement_details ad,tenancy_applications ta,application_details apd 
WHERE ad.AGR_APP_ID=ta.TAP_APP_ID AND apd.APP_ID=ta.TAP_APP_ID;

DECLARE CONTINUE HANDLER FOR NOT FOUND
SET no_more_rows = TRUE;
open lease_cursor;
select FOUND_ROWS() into num_rows;

get_lease:Loop
    FETCH lease_cursor INTO 
        tap_pro_id,
        tap_proposed_start_date,
        tap_proposed_end_date,
        tap_rent_type,
        tap_creation_ts,
        tap_usr_id,
        tap_com_id;

in above code i am able to get data into cursor but tap_proposed_start_date,tap_proposed_end_date always '0000-00-00'

Upvotes: 2

Views: 205

Answers (1)

nakul
nakul

Reputation: 533

i add DATE_FORMAT statement in select as 

DEClARE lease_cursor CURSOR FOR select ta.TAP_PROPERTY_ID,**DATE_FORMAT(ta.TAP_PROPOSED_START_DATE,'%Y-%m-%d'),DATE_FORMAT   (ta.TAP_PROPOSED_END_DATE,'%Y-%m-%d')**,ta.TAP_RENT_TYPE,
ad.AGR_CREATION_TS,ad.AGR_CREATED_BY,apd.APP_COM_ID
from agreement_details ad,tenancy_applications ta,application_details apd where  ad.AGR_APP_ID=ta.TAP_APP_ID and apd.APP_ID=ta.TAP_APP_ID;

Upvotes: 3

Related Questions