Conversion failed - SQL Server Forums

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)

Conversion failed

Author

Topic

juicyapple
Posting Yak Master

176 Posts

Posted - 2008-02-11 : 22:02:03

Hi, I get an error for the sql below, do I need to do conversion for @DATETM??


DECLARE @DATETM DATETIME;
DECLARE @nsql NVARCHAR(4000);
SET @DATETM = '20-OCT-2007';
SET @nsql = 'select count(*) from [SERVER1].[DB].[DBO].[tableA]
WHERE TempDt < ''' + @DATETM + '''';
EXEC sp_executesql @nsql;

Msg 241, Level 16, State 1, Line 4
Conversion failed when converting datetime from character string.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-02-11 : 22:22:12

you need to convert @DATETM to string first before concat

WHERE TempDt < ''' + convert(varchar(10), @DATETM, 112) + '''';

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-02-11 : 22:24:20

and you should use ISO format YYYYMMDD to avoid ambiguity of DD-MM-YYYY / MM-DD-YYYY


SET @DATETM = '20071020'

KH
[spoiler]Time is always against us[/spoiler]

juicyapple
Posting Yak Master

176 Posts

Posted - 2008-02-11 : 22:53:27

This statement will only return a varchar value but wouldn't change the data type of @DATETM to varchar right?

convert(varchar(10), @DATETM, 112)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-02-11 : 23:04:28

quote:
Originally posted by juicyapple

This statement will only return a varchar value but wouldn't change the data type of @DATETM to varchar right?

convert(varchar(10), @DATETM, 112)

Yes. You are right

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-12 : 01:56:55

quote:
Originally posted by juicyapple

Hi, I get an error for the sql below, do I need to do conversion for @DATETM??

DECLARE @DATETM DATETIME;
DECLARE @nsql NVARCHAR(4000);
SET @DATETM = '20-OCT-2007';
SET @nsql = 'select count(*) from [SERVER1].[DB].[DBO].[tableA]
WHERE TempDt < ''' + @DATETM + '''';
EXEC sp_executesql @nsql;

Msg 241, Level 16, State 1, Line 4
Conversion failed when converting datetime from character string.

DECLARE @DATETM DATETIME;
SET @DATETM = '20-OCT-2007';
select count(*) from [SERVER1].[DB].[DBO].[tableA]
WHERE TempDt < @DATETM

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources