sql - Query Result(NULL value/Datetime format) -

i have query use put rows in 1 column, dynamic because need @ least 8 tables:

declare @tblname varchar(20) = 'location' declare @columns nvarchar(max), @sql nvarchar(max)  select @columns = coalesce(@columns, '') + '+[' + column_name + '],+'''''',''''''' information_schema.columns table_name = @tblname , table_schema='les' select @columns  set @sql = 'select concat(''''''''' + stuff(@columns, 103,9, '') + '+'''''') ' + @tblname select @sql -------------------------------------------------------------------------- r1: select concat(''''+[location],+''','''+[location type],+''','''+[region],+''','''+[world region],+''','''+[refresh date]+''') location 

if execute query (without datetime column (refresh date) contains null values) result be

'0020319389','cmf','aj','aj' '0031209263','cmf','aj','aj' '01bm','dcl','eu','eu' '01cr','dcl','eu','eu' 

my problem here when execute query refresh_date column, error:

conversion failed when converting date and/or time character string.

can me please?

thanks

your problem you're mixing old school concatenation, + 2012/2014 concat function , data precedence rules in effect.

this bit of code using old school syntax

select @columns = coalesce(@columns, '') + '+[' + column_name + '],+'''''',''''''' information_schema.columns table_name = @tblname , table_schema='les' 

instead, make use concat, data type precedence convert date times string handle nulls.