Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

help with sql

Posted on 2013-06-11
4
Medium Priority
?
165 Views
Last Modified: 2013-06-27
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
Comment
Question by:welcome 123
  • 2
4 Comments
 
LVL 23

Accepted Solution

by:
Ioannis Paraskevopoulos earned 600 total points
ID: 39239059
Well,

You could add
and FY1<>'NULL'

Open in new window

In your where clause.

Giannis
0
 
LVL 4

Expert Comment

by:BAKADY
ID: 39239063
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
 
LVL 23

Expert Comment

by:Ioannis Paraskevopoulos
ID: 39239068
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39239933
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question