route217
asked on
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_ne w_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...
I am trying to eliminate/ignore all the null values in a given column and trying to use the following
Iif(isnull(fos_datetime_ne
But iam getting an error saing: the null function requires 2 argument(s). (#174)
not sure whats wrong...
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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..
In this example, if there is a value of MaxQty, it will show.. Otherwise, it shows as 0.00..
HTH,
Kent
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
In this example, if there is a value of MaxQty, it will show.. Otherwise, it shows as 0.00..
HTH,
Kent
ASKER
Hi Kent...
ok how do i change....
Iif(isnull(fos_datetime_ne w_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...
ok how do i change....
Iif(isnull(fos_datetime_ne
to ignore 0.00 values and show all + values...
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
or you may have to use:
To: isnull(fos_datetime_new_op
And to test..
SELECT isnull(fos_datetime_new_op
HTH,
Kent
ASKER
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)
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)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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.
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.
ASKER
So Paul
are you saying;
CAST(CONVERT(varchar(8), [fos_datetime_new_op], NULL, 112), AS datetime) AS [fos_datetime_new_op]),
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],
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
:) 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)"
select cast(convert(varchar, '2013/09/20', 112) as datetime) -->> works!
perhaps take out the size limitation "(8)"
ASKER
So
CAST( CONVERT(varchar,'2013/09/2 0', [fos_datetime_new_op], 112), AS datetime) AS [fos_datetime_new_op],
????
CAST( CONVERT(varchar,'yyyy/mm/d d'', [fos_datetime_new_op], 112), AS datetime) AS [fos_datetime_new_op],
CAST( CONVERT(varchar,'2013/09/2
????
CAST( CONVERT(varchar,'yyyy/mm/d
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
All the best. Cheers, Paul.
ASKER
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...