passing datetime variables into a bcp statement

Hi

I posted a question a while back about passing dates through a BCP SQL statement and received the answer that they should look as follows

declare @.sql as varchar(1000)

select @.sql = 'bcp "Exec CHC_Data_V2..TestSP ''05/01/07'', ''01/01/07''" queryout "c:\entitytext.txt" -SAJR\SQLEXPRESS -T -c -t'

exec master..xp_cmdshell @.sql

Now I need to do it differently and I have declared date variables and set the values and now i want to place the varaible names into the statement but i am receiving errors such as cannot convert character to datetime and once again i am looking for the correct way to type the bcp statement

I have the following example

Declare @.EndDate Datetime

Declare @.StartDate DateTime

Declare @.FilePath varchar (250)

Declare @.ServerName varchar (250)

Declare @.sql varchar(8000)

SET @.EndDate = '05/01/2007'

SET @.StartDate = '06/01/2007'

SET @.FilePath = 'C:\test.txt'

SET @.ServerName = 'SQLEXPRESSSERVERPATH'

select @.sql = 'bcp "Exec CHC_Data_V2..CHC_PRSACursor @.EndDate, @.StartDate " queryout "' + @.FilePath + '" -S' + @.ServerName + ' -T -c -t "|"'

exec master..xp_cmdshell @.sql

I have tried

select @.sql = 'bcp "Exec CHC_Data_V2..CHC_PRSACursor '' + @.EndDate+ '', '' + @.StartDate + ''" queryout "' + @.FilePath + '" -S' + @.ServerName + ' -T -c -t "|"'

And many many other variations but am mystified as to the correct format.

Can anyone help?

Syvers

Try:

select @.sql = 'bcp "Exec CHC_Data_V2..CHC_PRSACursor ' + @.EndDate + ', ' + @.StartDate + ' " queryout "' + @.FilePath + '" -S' + @.ServerName + ' -T -c -t "|"'

exec master..xp_cmdshell @.sql

|||

Code Snippet

select @.sql = 'bcp "Exec CHC_Data_V2..TestSP ''' + convert(varchar(10), @.EndDate, 101) + ''', '''+ convert(varchar(10), @.StartDate, 101) + '''" queryout "c:\entitytext.txt" -SAJR\SQLEXPRESS -T -c -t'

|||

Thanks Dale, my thinking was not on all cylinders this morning -had to rush out for a meeting.

|||

Team work!

|||Thank you for your help, works great now.