dynamic query throwing convertion error

• scottichrosaviakosmos

  SSCommitted

  Points: 1889

  More actions

  August 25, 2010 at 7:40 am

  #239471

  declare @a varchar(50)

  declare @b-2 varchar(20)

  set @b-2=(select gender from table where Cov='101')

  set @a=@b + convert(datetime,'24/08/2010',103)

  select CoveNoteNo from tableccr where Riskstart=@a

  the above query i m using to fetch data frm a table in database . now in my table the

  date is stored as 2010-08-24 00:00:00.000 and the datatype is datetime.

  now when i run this query i m getting error:

  Msg 241, Level 16, State 1, Line 4

  Conversion failed when converting datetime from character string.

  i know that i m getting this error because i am trying to concatonate a datetime value to

  varchar, but i want it in this fashion only.

  i want this same query to run beceause i m using same type of query in other program.

  i want a right query .

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  August 25, 2010 at 8:02 am

  #1211648

  try this:

  declare @a varchar(50)

  declare @b-2 varchar(20)

  set @b-2=(select gender from table where Cov='101')

  set @a='''' + @b-2 + ''''' OR CAST ( ' + convert(datetime,'24/08/2010',103) + ' AS DATETIME )'

• Rahul Prajapati

  SSC Eights!

  Points: 996

  More actions

  August 25, 2010 at 8:04 am

  #1211650

  Use This :

  SET @a = @b-2 + CAST (convert(datetime,'24/08/2010',103) AS VARCHAR(50))

• scottichrosaviakosmos

  SSCommitted

  Points: 1889

  More actions

  August 26, 2010 at 7:07 am

  #1212269

  this has eliminated the error but now no data is comming .

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  August 26, 2010 at 7:11 am

  #1212272

  because u are concatenating Gender with the Date and using this to match RiskStart..

  Your @a+@b results in Riskstart = 'M2010-08-25 00:00:00.000'.. is it wat you have it in RiskStart column ??

• Abhijit More

  SSCertifiable

  Points: 6897

  More actions

  August 26, 2010 at 7:47 am

  #1212308

  Or else you can try this

  declare @a varchar(50)

  declare @b-2 varchar(20)

  set @b-2='M'

  set @a=@b + convert(varchar(50),'24/08/2010',101)

  select @a

  Abhijit - http://abhijitmore.wordpress.com