Re: Bad Dates in Informix

Okay, but why doesn't CURRENT or TODAY work? If I understand the previous 2 posts, it seems that sometimes CURRENT and TODAY return "0" (which for DATE is equivalent to 12/31/1899). If this is correct, I would think this is a bug. Is this problem limited to Stored Procedures? Jeff Diaz wrote: > > Here is an example of the stored procedure that I use. You will see my > workaround (p_today) which, if just CURRENT used in its place, won't > work. You will see down within the FOREACH statement that I compare > dates. I believe that the p_assig_end_date should always have a valid > value, but I'll check to be sure it isn't null on occasion. Still, if > so, would that clear out the CURRENT date if I had CURRENT in place > of p_today? > > ----------------------------------------------------------------------- > CREATE PROCEDURE "informix".wo_triggers(p_branch CHAR(5), p_wo_num INT, > p_oldstatus CHAR(5), p_newstatus CHAR(5), > p_end_date DATE, p_flag CHAR(1) ) > > DEFINE p_assig_num INT; > DEFINE p_assig_end_date DATE; > DEFINE p_ssn CHAR(9); > > DEFINE logtext CHAR(100); > DEFINE p_today DATE; > > LET p_today = CURRENT; > > -- Update assignment end_date to workorder end_date and > -- employee status to ZZ > -- if Workorder Status = "C", "X", or "L" > IF p_oldstatus != p_newstatus AND p_flag = 'U' THEN > IF p_newstatus = "C" OR p_newstatus = "X" or p_newstatus = "L" THEN > -- This will kick off the assignment trigger which should set the employee > -- status to it's correct value if it isn't already. > FOREACH > SELECT assig_num, end_date > INTO p_assig_num, p_assig_end_date > FROM as_fo_assignment > WHERE branch = p_branch AND wo_num = p_wo_num> > IF p_assig_end_date > p_today THEN > UPDATE "informix".as_fo_assignment > SET end_date = p_today > WHERE branch = p_branch AND wo_num = p_wo_num > AND assig_num = p_assig_num; > END IF > END FOREACH > END IF > END IF > > END PROCEDURE;