error while running a simple sp.

Hi,

alter procedure sp_migrate_sg_oracle_vendoraddress1

as

insert into oracle..ps_vendor_address

SELECT a.AddressSeq as address_seq_num,a.addressLine1 as address1,

a.AddressLine2 as address2, a.City , a.State, a.Country, a.Postcode,

a.AddressLine3 as address3,a.AddressLine4 as address4, a.County,

a.Email as emailid, cast(a.effectivedate as char(10)) as effdt,a.Status as eff_status,

v.txtvendorname1 as name1,txtvendorname2 as name2,num1=null,

num2=null,housetype=null,addr_field1=null,addr_field2=null,addr_field3=null,

geo_code=null,in_city_limit='N',v.txtSetID as setid,v.txtVendorID as vendorid

FROM sf_stagingoutbound..AP_Address as a

left outer join vw_ap_vendorssubmitted v

on v.EFOLDERID = a.EFOLDERID

when iam executing this iam repeatedly getting the following error.

Msg 241, Level 16, State 1, Procedure sp_migrate_sg_oracle_vendoraddress1, Line 3

Conversion failed when converting datetime from character string.

pls help.

thanks and Regards,

sg

your cast is wrong, take a look at this

declare @.effectivedate datetime
select @.effectivedate = getdate()

select @.effectivedate,cast(@.effectivedate as char(10)) as effdt, convert(char(8),@.effectivedate,112) as effdt2

if you run this you will see that your cast returns Jan 30 200

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

try this instead

select convert(varchar,getdate(), 106) -- 30 Jan 2007
select convert(varchar,getdate(), 107) -- Jan 30, 2007