passing datetime variables into a bcp statement

Transact-SQL

syvers


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

Top

Re: passing datetime variables into a bcp statement

Arnie Rowland


Try:

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

exec master..xp_cmdshell @sql






Top

Re: passing datetime variables into a bcp statement

DaleJ

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'






Top

Re: passing datetime variables into a bcp statement

Arnie Rowland

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





Top

Re: passing datetime variables into a bcp statement

DaleJ

Team work!





Top

Re: passing datetime variables into a bcp statement

syvers

Thank you for your help, works great now.


Top