I am working on SQL 2000 with an AS400 as a linked server. I have a char field that I would like to format with a slash. This field is actually a date field and it is ccyymmdd. I used a substring to pull out the appropriate positions to reformat to a string field with slashes in the proper date positions. I cannot get this statement to work inside of the linked server openquery statement.
It tells me:
Invalid operator for data type. Operator equals divide, type equals varchar.
I tried to make the / a variable and it did not like that .
Any ideas are appreciated
Here is the statement.
set @sql='SELECT * FROM OPENQUERY(AS400,''SELECT LPHSP# As HospNum, LPIPOP as InOutPat, PESPID As SPID, LPROOM as PatRoom,
LPBED as PatBed, PESEX as PatSex, PERACE as PatRace, PEETHN AS PatEthn, LPAGE AS PatAge, LPACCT AS PatAcctNum,
LPMRC# AS PatMRNum, PELSTN as PatLName, PEFSTN as PatFName, PEDOB8 as PatDOB,MRDNM AS DRName, PEADR1 AS PatAdd1,
PEADR2 AS PatAdd2, PECITY as PatCity, PESTE as PatState, PEPSTL AS PatZip, LPADT1 as AdmMonth, LPADT2 as AdmDay,
LPCEN1 as AdmCen, LPADT3 as AdmYear, HBINS# as InsCoNum, HBPLAN as InsPlanCode, HBPTY as InsPriortyCode,
HBVST as InsVerStat, HBINM as InsCoName, HBIID# as InsCoNum, PEMAR AS PatMarStat,
substring(cast(Cast(PEDOB8 as numeric(8))as char(8)),5,2)concat'/'
concat substring(cast(Cast(PEDOB8 as numeric(8))as char(8)),7,2)concat '/'
concat substring(cast(Cast(PEDOB8 as numeric(8))as char(8)),1,4)as dob
LEFT OUTER JOIN GSHBSYDTAA.BADMPEU ON LPSPID=PESPID
LEFT OUTER JOIN GSHBSYDTAA.BADPHBP ON HBACCT=LPACCT AND HBMRC#=LPMRC#
LEFT OUTER JOIN GSHBSYDTAA.BSYMMRP ON LPHSP#=MRHSP# AND LPADR#=MRDR# WHERE LPHSP#=101 AND LPACCT=456533'')'