Search code examples
sql-servert-sqlbcp

BCP converting DATE to VARCHAR

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.

Solution

  • 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'