SQL Error on insull string

Hi Experts

I am trying to eliminate/ignore all the null values in a given column and trying to use the following

Iif(isnull(fos_datetime_new_op),””, CAST(CONVERT(varchar(8), [fos_datetime_new_op], 112) AS datetime) AS [fos_datetime_new_op]),

But iam getting an error saing: the null function requires 2 argument(s). (#174)

not sure whats wrong...
route217JuniorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent DyerIT Security Analyst SeniorCommented:
Iif(isnull(fos_datetime_new_op),"", CAST(CONVERT(varchar(8), [fos_datetime_new_op], 112) AS datetime) AS [fos_datetime_new_op]),

Looking on TechNet, we see:

http://technet.microsoft.com/en-us/library/ms184325.aspx


Syntax

ISNULL ( check_expression , replacement_value )


It requires two values..


ISNULL(MaxQty, 0.00)

Please read the following note on the page:

C. Testing for NULL in a WHERE clause

Do not use ISNULL to find NULL values. Use IS NULL instead. The following example finds all products that have NULL in the weight column. Note the space between IS and NULL.

HTH,

Kent
0
route217JuniorAuthor Commented:
Hi Kent

thanks for the feedabck.....but i am confused....i am not a sql developer or sql code writer...

i am trying to amend the sql above so it ignores all NUll values and return back a result,,,not sure how to amend the above string...
0
Kent DyerIT Security Analyst SeniorCommented:
You need two values for it..


ISNULL ( check_expression , replacement_value )

If it is null against a value replace it with another.

Let's take the example MS published on the page..
USE AdventureWorks2012;
GO
SELECT Description, DiscountPct, MinQty, ISNULL(MaxQty, 0.00) AS 'Max Quantity'
FROM Sales.SpecialOffer;
GO

Open in new window


In this example, if there is a value of MaxQty, it will show.. Otherwise, it shows as 0.00..

HTH,

Kent
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

route217JuniorAuthor Commented:
Hi Kent...

ok how do i change....
Iif(isnull(fos_datetime_new_op),"", CAST(CONVERT(varchar(8), [fos_datetime_new_op], 112) AS datetime) AS [fos_datetime_new_op]),

to ignore 0.00 values and show all + values...
0
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
IIF is MS Access, and ISNULL is a boolean function testing for NULL there. Further, an empty string is represented by '', not "". For MSSQL, you will need to transform the syntax to:
ISNULL( CAST(CONVERT(varchar(8), [fos_datetime_new_op], 112) AS datetime) , '' )
  AS [fos_datetime_new_op]),

Open in new window

or better, but less obvious
CAST(ISNULL( CONVERT(varchar(8), [fos_datetime_new_op], 112), '') AS datetime) 
  AS [fos_datetime_new_op]),

Open in new window

0
Kent DyerIT Security Analyst SeniorCommented:
Just focus on the isnull portion for now..

I don't know if you have SQL Studio or not..

But it may prove helpful to test this..

Change yours from :  isnull(fos_datetime_new_op)

To:  isnull(fos_datetime_new_op, 00-00-0000)

or you may have to use:
To:  isnull(fos_datetime_new_op, '00-00-0000')

And to test..

SELECT  isnull(fos_datetime_new_op, 00-00-0000), USER_NAME FROM SOMETABLE (NOLOCK)

HTH,

Kent
0
route217JuniorAuthor Commented:
Kent

using

 CAST(ISNULL( CONVERT(varchar(8), [fos_datetime_new_op], 112), '') AS datetime)
  AS [fos_datetime_new_op]),

i get the error message: incorrect syntax near')' (#102)
0
PortletPaulfreelancerCommented:
the answer above is correct for this question. ISNULL() requires 2 arguments.

I'd also like to point out that IIF is just another way of writing a case expression (in fact IIF gets interpreted as a case expression) I always suggest you go straight to case instead of IIF. for more: http://technet.microsoft.com/en-us/library/hh213574.aspx

Even with IIF, or CASE, you are still going to bump into type conversion issues as soon as this part is evaluated:

CONVERT(varchar(8), [fos_datetime_new_op], 112)


If I recall your situation the incidence of valid data very low

Have you inspected that data in detail?
do you have small representative samples you can share?
0
route217JuniorAuthor Commented:
Paul....

sorry cannot share any data---extermely stressfull and apologies...

i am gettung te sql guys to look into this the data in detail....

in the mean time just investigate and looking at option....to make this work...

i appreicate all the help/assist on the excellent EE.....

thanks
ALL
0
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
route217,
The code in http:#a39489490 is my suggestion, not Kent's. To get rid of the syntax error, just remove the very last closing parenthesis.

Paul,
select convert(varchar(8), NULL, 112) will work fine.
0
route217JuniorAuthor Commented:
So Paul

are you saying;

CAST(CONVERT(varchar(8), [fos_datetime_new_op], NULL, 112), AS datetime) AS [fos_datetime_new_op]),
0
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
I'm not Paul either, and the formula is
CAST(ISNULL( CONVERT(varchar(8), [fos_datetime_new_op], 112), '') AS datetime) 
  AS [fos_datetime_new_op],

Open in new window

0
PortletPaulfreelancerCommented:
Qlemo
:) agreed:: select convert(varchar(8), NULL, 112) will work fine.

from details from previous questions, the field in question has data that isn't converting to datetime, however it's actually the cast(... as datetime) that's failing, "my bad".

e.g.
select cast(convert(varchar(8), '2013/09/20', 112) as datetime) -->> fails

the point is we don't really know what is in that field.

@route217
all I'm looking for are sample of that single column if that helps
0
PortletPaulfreelancerCommented:
actually that made me think...

select cast(convert(varchar, '2013/09/20', 112) as datetime) -->> works!

perhaps take out the size limitation "(8)"
0
route217JuniorAuthor Commented:
So

CAST( CONVERT(varchar,'2013/09/20', [fos_datetime_new_op], 112),  AS datetime) AS [fos_datetime_new_op],

????

CAST( CONVERT(varchar,'yyyy/mm/dd'', [fos_datetime_new_op], 112),  AS datetime) AS [fos_datetime_new_op],
0
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Paul,
If you use a varchar const as a date, you need to convert it to a date first ;-). Then you can cut/convert to varchar(8).

route217,
No. If the column fos_datetime_new_op is a datatime, my formula should work fine. If it is varchar, all kind of conversion errors may happen, depending on the actual content. The difference is that a datetime column always contains a valid date (or NULL), but a varchar might contain "n/a" and the like, which certainly is no valid date.
0
PortletPaulfreelancerCommented:
>>" If it is varchar, all kind of conversion errors may happen, depending on the actual content."
bingo! that's what I believe is the case.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
I'm hoping you did manage to get past these conversion issues (& maybe the "sql guys" helped out). It can be v.painful when what looks like dates is stored as strings.

All the best. Cheers, Paul.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.