Don't know exactly,.,.

Question

Post reply

Don't know exactly,.,.

MrReddy

Ten Centuries

Points: 1083
More actions
June 8, 2012 at 4:09 pm

#262735

Hello All,
I really don't know what my mistake is,.,
When I run my query in dataflow task OLEDB source, build query, its working fine but when I parse the query it says incorrect syntax near ')'.
My query is like this..
select
id,column1,id_date from SII.COMBO_2011_VW
where convert(varchar,ID) =? and YEAR=? and
convert(date,left(id_date,CHARINDEX('-',id_date,1)-1)+','+'20'+right(id_date,2)) between
convert(varchar,CONVERT(date, DATEADD(mm, DATEDIFF(mm, 0, convert(date,left((?),CHARINDEX('-',(?),1)-1)+','+'20'+right((?),2))) - 13, 0))) and
convert(varchar,convert(date,left((?),CHARINDEX('-',(?),1)-1)+','+'20'+right((?),2)))
here my id_date is like Jan-12.
Can you please help me..
Thanks,

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

It might help to write the query in a more readable format:

DECLARE @ID= ?;

DECLARE @Year= ?;

DECLARE @Param1= ?;

DECLARE @Param2= ?;

DECLARE @Param3= ?;

DECLARE @Param4= ?;

DECLARE @Param5= ?;

SELECT

id

,column1

,id_date

FROM SII.COMBO_2011_VW

WHERECONVERT(VARCHAR(30),ID) = @ID

AND YEAR= @Year

AND CONVERT(DATE,LEFT(id_date,CHARINDEX('-',id_date,1)-1) + ',' + '20' + RIGHT(id_date,2)) BETWEEN

CONVERT(VARCHAR(30),CONVERT(DATE,DATEADD(mm,DATEDIFF(mm,0,CONVERT(DATE,LEFT((@Param1),CHARINDEX('-',(?),1)-1)+','+'20'+RIGHT((@Param2),2))) - 13, 0)))

AND CONVERT(VARCHAR(30),CONVERT(DATE,LEFT((@Param3),CHARINDEX('-',(@Param4),1)-1)+','+'20'+RIGHT((@Param5),2)));

The extensive use of functions will also make sure not a single index will be used for your query (except maybe for YEAR).

Maybe you should try to store your date data in a more convenient date format (use a real datetime datatype if you haven't already) so that you don't need to use that many functions.

Furthermore, specify a length for CONVERT(VARCHAR,xxx). This one defaults to a length of 30, which can be confusing.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

MrReddy Ten Centuries Points: 1083 More actions · Answer 2

Thanks Koen,

I solved this issue using 3 excute sql tasks and is working fine now.