SQL Error on insull string

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

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

Select allOpen in new window

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

HTH,

Kent

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

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

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)

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

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.

So Paul

are you saying;

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

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],

Select allOpen in new window

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

actually that made me think...

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

perhaps take out the size limitation "(8)"

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],

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.

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.