SQL Conditional Cast VARCHAR for FLOAT

Question

When I run the following query I get error "Cannot cast VARCHAR to FLOAT". The problem is some else on my team designed the DB a while back and put Milliseconds as varchar and sSometime the value isn't there so he put "NA" instead.

Now when I cast the value for Milliseconds to take the AVE(), "NA" cannot be casted. Is there a way to define default value for "NA", like:

IF a.Milliseconds == "NA" 
     0
ELSE
     CAST(a.Milliseconds AS FLOAT)



SELECT 
  b.Date, 
  AVG(CAST(a.Milliseconds AS FLOAT)) AS Milliseconds,
FROM Fields a
INNER JOIN Cycles b
ON a.CyclesId = b.Id
GROUP BY b.Date

Sample Data

CyclesId | Milliseconds
1        | 24.1557
2        | 23.4886
3        | NA

Answer 1

Use a CASE statement around your value

SELECT 
  b.Date, 
  AVG(CAST(CASE WHEN a.Milliseconds = 'NA' THEN '0' ELSE a.Milliseconds END AS FLOAT)) AS Milliseconds,
FROM Fields a
INNER JOIN Cycles b
    ON a.CyclesId = b.Id
GROUP BY b.Date

If you want the NA values excluded all together then you will do the following:

SELECT 
  b.Date, 
  AVG(CAST(a.Milliseconds AS FLOAT)) AS Milliseconds,
FROM Fields a
INNER JOIN Cycles b
    ON a.CyclesId = b.Id
WHERE a.Milliseconds != 'NA'
GROUP BY b.Date

see SQL Fiddle with Demo