SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Date Conversion with Dynamic SQL
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

qman
Constraint Violating Yak Guru

USA
440 Posts

Posted - 02/12/2013 :  09:10:40  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3655 Posts

Posted - 02/12/2013 :  09:31:29  Show Profile  Reply with Quote
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

Edited by - James K on 02/12/2013 09:54:05
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000