Opequery question

[bazooka]

I am getting the following error

Syntax error conveg datetime from character string.

When I try to run the following query.


I am getting the error in the last area where I am trying to pass a variable for the EFFDATE.

Thanks for any help
Sathya

[skhanal]

Is EFFDATE defined as char datatype?

[bazooka]

Yes it is a character data type.

I even tried to convert the Effective date as a datetime type outside the openquery and tried using it. But it gives the same error.

Thanks for ur help

[MAK]

Try this

SET @SQL = 'SELECT T3.ORG_DEPARTMENT, T1.EMPLID, T2.EMPLNAME

FROM

openquery(SQL2, ''select EMPLNAME,EFFECTIVE_DATE,ORGID,EMPLID, ANNBASRT, SHIFT_AMOUNT
from TECHRIS.DBO.TRBASCTB'') as T2,
openquery(SQL2, ''select ORG_DEPARTMENT, ACCOUNT_NBR, FISC_YEAR
from TECHFIM.DBO.TFORGCTB
WHERE ORG_DEPARTMENT = ''''' + @DEPT + ''''''') as T3,
openquery(SQL2, ''select ORGID, TBLGRPCD, JOBCLASS, EFFECTIVE_DATE, STATUS, JOBTITLE
from TECHRIS.DBO.TRJOBCTB
WHERE TBLGRPCD = ''''THJOB''''
AND STATUS = ''''A'''''') as T4
WHERE T1.EMPLID = T2.EMPLID
AND T1.ACCOUNT_NBR10 = T3.ACCOUNT_NBR
AND T1.JOBCLASS = T4.JOBCLASS
AND T1.FISCAL_YR = ''2004''
AND T3.FISC_YEAR = ''' + @CURYEAR + '''
AND T1.ENTITY IN (''' + @ENTITY1 + ''',''' + @ENTITY2 + ''')
AND T2.EFFECTIVE_DATE =(SELECT MAX(convert(datetime,T5.EFFECTIVE_DATE) )
FROM openquery(SQL2, ''select ORGID,EMPLID,ENTYCODE,EFFECTIVE_DATE
From TECHRIS.DBO.TRBASCTB'') as T5
WHERE T5.ORGID = T2.ORGID
AND T5.EMPLID = T2.EMPLID
AND EFFECTIVE_DATE <= ''' + @EDATE + '''))
ORDER BY T3.ORG_DEPARTMENT, T2.EMPLNAME'


exec (@SQL)
GO

[bazooka]

Thanks Mak. I have tried that thing.. It gives me the same error.


Thanks for ur help

[MAK]

what is the date value that @EDate carries. when you have some wrong value u will get this error message

example:


set quoted_identifier off
declare @date1 varchar(100)
declare @string1 varchar(1000)
set @date1="5/1/2003"
set @string1 = 'select convert(datetime,"'+@date1 +'")'
print @string1
exec (@string1)

result:
Syntax error converting datetime from character string.

but the following will run properly

set quoted_identifier off
declare @date1 varchar(100)
declare @string1 varchar(1000)
set @date1="5/1/2003"
set @string1 = 'select convert(datetime,"'+@date1 +'")'
print @string1
exec (@string1)

result:
select convert(datetime,"5/1/2003")

2003-05-01 00:00:00.000

[bazooka]

Thank you Mak... It is now working.. the key was that, i did not specify the length in Varchar type of the Effective date.

Thanks again

Sathya

[MAK]

Cool. I always trouble shoot things like with sample known data.

Hope it helped