Convert a value from scientific notation to decimal in T-SQL

A VARCHAR value that is in scientific notation can be converted to a DECIMAL value by first converting it to a REAL value, then to the DECIMAL value.

For example:

DECLARE @s VARCHAR(10) = '9.0E-4';
SELECT CONVERT(DECIMAL(8, 4), CONVERT(REAL, @s));

This also works with CAST.