Date Conversion with Dynamic SQL

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 2008 Forums

Transact-SQL (2008)

Date Conversion with Dynamic SQL

Author

Topic

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2013-02-12 : 09:10:40

I am getting a "Conversion failed when converting date and/or time from character string." error when executing this sample dynamic

Would anyone be so kind as to help me out with the conversion?
If I change @whatDate to varchar(10), I get no records returned when I know they exist... Also, I need this to be dynamics SQL....

Thanks!

SQL code:


declare @whatDate datetime;
declare @whatDB char(20);
declare @sql nvarchar(4000);

set @whatDate = '02/05/2013'
set @whatDB = 'TEST';

set @sql = 'select ard.aaa, ard.bbb, ard.ccc,
ba.aaa, ba.bbb from ' + @whatDB + '.dbo.myTable ard ' + 'inner join ' + @whatDB + '.dbo.myTable2 ba on ard.aaa = ba.aaa ' + 'where ard.bbb in(''A'',''B'',''C'') and ard.testDate = ' + @whatDate + 'order by ard.aaa desc'
exec(@sql)

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-12 : 09:31:29

I made a few changes - see in RED:

DECLARE @whatDate NCHAR(8)
DECLARE @whatDB NVARCHAR(20);
DECLARE @sql NVARCHAR(4000);

SET @whatDate = '20130205'
SET @whatDB = 'TEST';

SET @sql = 'select ard.aaa, ard.bbb, ard.ccc,
ba.aaa, ba.bbb from ' + @whatDB + '.dbo.myTable ard ' + 'inner join ' + @whatDB 
    + '.dbo.myTable2 ba on ard.aaa = ba.aaa ' + 
    'where ard.bbb in(''A'',''B'',''C'') and ard.testDate = ''' + @whatDate + 
    ''' order by ard.aaa desc'

exec(@sql)

You will notice I changed the date format. See here to see why this is recommended: http://visakhm.blogspot.com/2011/12/why-iso-format-is-recommended-while.html

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources