dynamic query generating convertion error

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

dynamic query generating convertion error

Author

Topic

scottichrosaviakosmos
Yak Posting Veteran

66 Posts

Posted - 2010-08-25 : 09:34:37

declare @a varchar(50)
declare @b varchar(20)
set @b=(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 .

scoo

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-08-25 : 09:36:21

declare @a varchar(50)
declare @b varchar(20)
set @b=(select gender from table where Cov='101')
~~set @a=@b + convert(datetime,'24/08/2010',103)~~
set @a=@b + convert(varchar(12),'24/08/2010',103)

select CoveNoteNo from tableccr where Riskstart=@a

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-25 : 09:41:58

really didnt understand why you're concatenatng gender with date

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

scottichrosaviakosmos
Yak Posting Veteran

66 Posts

Posted - 2010-08-26 : 02:38:27

i have tried this option of converting into varchar but still i m getting convertion error.
is that because of the datetime is stored in a specific format in system .
This scenario is i m using in making a dynamic query where i m fetching data from a table where data is stored in datetime format and i m passing the value in from parameter in varchar format .because of this i m getting an error of conversion.

scoo

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-08-26 : 10:52:58

show the real query then

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-26 : 11:08:34

quote:
Originally posted by scottichrosaviakosmos

i have tried this option of converting into varchar but still i m getting convertion error.
is that because of the datetime is stored in a specific format in system .
This scenario is i m using in making a dynamic query where i m fetching data from a table where data is stored in datetime format and i m passing the value in from parameter in varchar format .because of this i m getting an error of conversion.

scoo

if you passing date values from varchar variable, try to pass it in iso format

CONVERT(datetime,@Yourvariable,121)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources