I have searched both this great forum and googled around but unable to resolve this.
We have two tables (and trust me I have nothing to do with these tables). Both tables have a column called eventId
.
However, in one table, data type for eventId
is float
and in the other table, it is nvarchar
.
We are selecting from table1
where eventI
is defined as float
and saving that Id into table2
where eventId
is defined as nvarchar(50)
.
As a result of descrepancy in data types, we are getting error converting datatype nvarchar
to float
.
Without fooling around with the database, I would like to cast the eventId
to get rid of this error.
Any ideas what I am doing wrong with the code below?
SELECT
CAST(CAST(a.event_id AS NVARCHAR(50)) AS FLOAT) event_id_vre,
The problem is most likely because some of the rows have event_id
that is empty. There are two ways to go about solving this:
float
to nvarchar
, rather than the other way around - This conversion will always succeed. The only problem here is if the textual representations differ - say, the table with float
-as-nvarchar
uses fewer decimal digits, orThe second solution would look like this:
SELECT
case when a.eventid <> ''
then cast(cast(a.event_id as nvarchar(50)) as float)
ELSE 0.0
END AS event_id_vre,
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With