Thread: date and plpgsql error
<p>I am trying to insert a date into a table with type date.<p>CREATE TABLE member_billing_info (<br />userid varchar(30)primary key,<br />creditcard_type varchar(30),<br />creditcard_number varchar(50),<br />****exp_date date,<br/>first_name varchar(100),<br />last_name varchar(100),<br />street_address varchar(100),<br />city varchar(100),<br/>state varchar(30),<br />zip_code varchar(30),<br />country varchar(100),<br />phone_number varchar(30),<br/>email varchar(200),<br />date_entered timestamp,<br />last_update_time timestamp<br />);<br /><p>I get errorslike this<p>WARNING: Error occurred while executing PL/pgSQL function enrollpayee<br />WARNING: line 27 at SQL statement<br/>ERROR: column "exp_date" is of type date but expression is of type character varying<br /> You willneed to rewrite or cast the expression<br /><p>=== this is my function ======<p>CREATE or replace FUNCTION enrollpayee(character varying, integer,character varying, character varying,<br /> text,character varying,character varying,charactervarying,<br /> character varying,character varying,character varying,character varying,<br /> charactervarying,character varying) RETURNS integer<br /> AS 'DECLARE<br /> <br /> strUserId ALIAS FOR $1;<br /> intPlanIdALIAS FOR $2;<br /> strCardType ALIAS FOR $3;<br /> cryptCreditCard ALIAS FOR $4;<br /> strExpDate ALIAS FOR$5;<br /> strFirstName ALIAS FOR $6;<br /> strLastName ALIAS FOR $7;<br /> strAddress ALIAS FOR $8; <br /> strCity ALIASFOR $9;<br /> strState ALIAS FOR $10;<br /> strZipCode ALIAS FOR $11;<br /> strCountry ALIAS FOR $12;<br /> strEmailALIAS FOR $13;<br /> strPhone ALIAS FOR $14;<p> v_Status integer :=1;<br /> v_LifeTime integer :=0;<br /> v_Successinteger :=1; <br /> <br /> v_ExpDate date;<br />BEGIN<p> v_ExpDate := CAST(strExpDate as DATE);<p>INSERT INTOmember_billing_info values(strUserId,intPlanId,strCardType,cryptCreditCard,v_ExpDate,strFirstName,strLastName,strAddress,strCity,strState,strZipCode,strCountry,strPhone,strEmail,now(),now());<p><br />RETURNv_Success;<p> <br />END;<p>'<br /> LANGUAGE plpgsql;<br /> <br /> ===========<p>the date text has theformat of this "04/15/2003". I also tried "2003-04-15". and I also tried in the function argument list using the typedate.<p>Nothing worked for me. Help.<p>thanks<p> <p> <p> <p><br /><hr size="1" />Do you Yahoo!?<br /><a href="http://rd.yahoo.com/webhosting/mail_tagline/evt=7748/*http://webhosting.yahoo.com/ps/wh3/prod/">Yahoo!Web Hosting</a>- establish your business online
On Thu, 13 Mar 2003, Christopher Smith wrote: > I am trying to insert a date into a table with type date. > CREATE TABLE member_billing_info ( > userid varchar(30) primary key, > creditcard_type varchar(30), > creditcard_number varchar(50), > ****exp_date date, > first_name varchar(100), > last_name varchar(100), > street_address varchar(100), > city varchar(100), > state varchar(30), > zip_code varchar(30), > country varchar(100), > phone_number varchar(30), > email varchar(200), > date_entered timestamp, > last_update_time timestamp > ); > CREATE or replace FUNCTION enrollpayee (character varying, integer,character varying, character varying, > text,character varying,character varying,character varying, > character varying,character varying,character varying,character varying, > character varying,character varying) RETURNS integer > AS 'DECLARE > > INSERT INTO member_billing_info > values(strUserId,intPlanId,strCardType,cryptCreditCard,v_ExpDate, > strFirstName,strLastName,strAddress,strCity,strState, > strZipCode,strCountry,strPhone,strEmail,now(),now()); > Umm, it seems to me that your insert is off on columns. You're insertingvarchar, int, varchar, varchar, date, ... into a table ofvarchar, varchar, varchar, date I don't see an int anywhere in the table, so I'm not sure what you were intending to do.