BCP converting DATE to VARCHAR

Question

I can't save file. I don't know why

DECLARE @allquery varchar(8000)

SET @allquery = ' bcp "SELECT ''a'' UNION ALL SELECT ' + CAST(CAST(GETDATE()as date) as char(200)) + ' FROM rozklad.dbo.rozklad" queryout D:\bcp\tmp.txt -S '+@@SERVERNAME+' -T -w -t,'

exec xp_cmdshell @allquery

I get this error

Error = [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]
Conversion failed when converting the varchar value 'a' to datatype int.

score 0 · Accepted Answer · answered Mar 24 '16 at 20:04

You are missing some single quotes. Try this

DECLARE @allquery varchar(8000)
SET @allquery = ' bcp "SELECT ''a'' UNION ALL SELECT ''' + CAST(CAST(GETDATE()as date) as char(200)) + ''' FROM rozklad.dbo.rozklad" queryout D:\bcp\tmp.txt -S '+@@SERVERNAME+' -T -w -t,'
exec xp_cmdshell @allquery

Your initial query was writing something like

SELECT 'a'
UNION ALL 
SELECT 2016-03-24

This results in an error because 2016-03-24 results in 1989 which is an INT.

Now it will be like

SELECT 'a'
UNION ALL 
SELECT '2016-03-24'

score 0 · Answer 2 · answered Mar 24 '16 at 20:05

You have this part outside your quotes:

CAST(CAST(GETDATE()as date) as char(200))

It should be inside the quotes so it is evaluated by SQL.

"SELECT ''a'' UNION ALL SELECT CAST(CAST(GETDATE()as date) as char(200)) FROM ...

Also, you should consider a smaller size for your cast of date to char. When would you have a result that's char(200) ?

BCP converting DATE to VARCHAR

2 Answers2