Can't convert '' to a timestamp.
I have a "date_column" (date type) from witch I want to select the top 1 row. I have a procedure that goes trough 10 tables, each table has the column "date_column" and I want to get the top 1 date from each column and create a view based on that date. I tried with a convert statement, and get: cant convert '' to a timestamp error. If i make @top_date (date type) i get an cant convert to numeric in the view creation step, so it has to be varchar I think.
declare @table_name varchar(100)
declare @top_date varchar(20)
declare @sql varchar(1000)
select @table_name = 'random_name'
select @top_date = (select top 1 convert(varchar(12),date_column,112) from @table_name)
set @sql = ('create view top_view as select * from ' + @table_name + ' where date_column = '''+ @top_date +'''')
execute @sql
As the error message pretty much suggests, you can't convert a blank string to a datetime. I am guessing the below query fetches blank string:
select top 1 convert(varchar(12),date_column,112) from @table_name
So to get the output, just add this small check.
where convert(varchar(12),date_column,112) <> ''
FINAL CODE
declare @table_name varchar(100)
declare @top_date varchar(20)
declare @sql varchar(1000)
select @table_name = 'random_name'
select @top_date = (select top 1 convert(varchar(12),date_column,112) from @table_name where convert(varchar(12),date_column,112) <> '')
set @sql = ('create view top_view as select * from ' + @table_name + ' where date_column = '''+ @top_date +'''')
execute @sql