Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 166
  • Last Modified:

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
0
welcome 123
Asked:
welcome 123
  • 2
1 Solution
 
Ioannis ParaskevopoulosCommented:
Well,

You could add
and FY1<>'NULL'

Open in new window

In your where clause.

Giannis
0
 
BAKADYCommented:
select * from temp where case FY1 when NULL then 0 else case when FY1= (YEAR(DATEADD(month, 6 + DATEDIFF(month, 0, getdate()), 0))) then 1 else 0 end end = 1

Open in new window

0
 
Ioannis ParaskevopoulosCommented:
Or even change your where to

CASE WHEN FY1 = 'null' THEN NULL ELSE FY1 END = (YEAR(DATEADD(month, 6 + DATEDIFF(month, 0, getdate()), 0)))

Open in new window


Giannis
0
 
PortletPaulCommented:
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'

Open in new window

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.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now