welcome 123
asked on
help with sql
I have a table where some of the columns have the value 'NULL' instead of null values so my query below fails and I need a way to fix the query, I have limited control on data:
select * from temp where
FY1= (YEAR(DATEADD(month, 6 + DATEDIFF(month, 0, getdate()), 0)))
and tempid=3145
here the 3145 has the FY1 value as 'NUll' where as others have the value either like 2013 or null and the column is varchar(4)
all I am doing is bring in data which matched this fiscal year
and with the above query I am getting the error :
Conversion failed when converting the varchar value 'NULL' to data type int.
where as the same works for null values
select * from temp where
FY1= (YEAR(DATEADD(month, 6 + DATEDIFF(month, 0, getdate()), 0)))
and tempid=3145
here the 3145 has the FY1 value as 'NUll' where as others have the value either like 2013 or null and the column is varchar(4)
all I am doing is bring in data which matched this fiscal year
and with the above query I am getting the error :
Conversion failed when converting the varchar value 'NULL' to data type int.
where as the same works for null values
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Or even change your where to
Giannis
CASE WHEN FY1 = 'null' THEN NULL ELSE FY1 END = (YEAR(DATEADD(month, 6 + DATEDIFF(month, 0, getdate()), 0)))
Giannis
I'd strongly recommend avoiding the implied conversion of the data to int by converting the single date calculation to varchar
select
*
from temp
where FY1= convert(varchar,(YEAR(DATEADD(month, 6 + DATEDIFF(month, 0, getdate()), 0))))
or FY1 is null
or FY1 = 'NULL'
That solves the conversion issue, but what do you do with (true) nulls and the string 'NULL'?? i.e. how do you know what fiscal year those records belong to? If you are simply going to ignore those, just remove the last 2 lines above.
Open in new window