CODEWITHSUNDEEP
t-sqlsap-iq
theweeknd
theweeknd

Reputation: 287

Why do i get an error: cant convert '' to a timestamp?

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

Upvotes: 1

Views: 483

Answers (1)

SouravA
SouravA

Reputation: 5243

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

Upvotes: 2

Related Questions