How to get this output

Hi evrybody
i have a question like this
i need to display the row data like this
If i use this sql
select datediff(yyyy,'01/01/2001','01/01/2004')
i get the output = 4

problem is sometime first parameter will be null
like this
select datediff(yyyy,NULL,'01/01/2004'),'N/A')
in that case my result looks like NULL

But what i need to see is if the result is null then i need to display like this N/A

How to do this task ?
regards
suis

Hey,

You can use the isnull method, so that if a value is null, it will return another value, like 'N/A' in this case. Though I don't know how that could work in a datediff method. Rather, you may want to wrap the isnull around the entire datediff call, so that if it returns null, you can return 'N/A'.

|||Hi i already used isnull
like this way
select isnull(datediff(yyyy,NULL,'01/01/2004'),'N/A')
but it giving me error !

"Conversion failed when converting the varchar value 'N/A' to data type int."
regards
suis|||

Try changing this:

select isnull(datediff(yyyy,NULL,'01/01/2004'),'N/A')

to

select isnull(convert(varchar(4), datediff(yyyy,NULL,'01/01/2004')),'N/A')

|||Hi Kent
Thanks for ur comments,
its worked out
regards
suis

sql

How to get this out put ?

Hi everybody
I have a requirement like this
I have a table call Child which looks like following

ChildId ChildAgeByYear ReferralSoure NoOfReferral
Ch01 02 Self 2
Ch01 02 Open 1
Ch03 02 Self 1

Now my problem is I need to display the above Child table information like this

AgeGroup ReferralSoure NoOfReferral
0 0
1 0
2 Self 3
Open 1
3 0
4 0

I need to count the child age group by child age like, 1, 2, 3, 4, and their ReferralSource and NoOfReferral for each child depends on the ReferralSource

Any Idea ?

Regards
Suis

Perhaps something like this:

declare @.child table
( ChildId varchar(5),
ChildAgeByYear integer,
ReferralSource varchar(12),
NoOfReferral integer
)
insert into @.child
select 'Ch01', 2, 'Self', 2 union all
select 'Ch01', 2, 'Open', 1 union all
select 'Ch03', 2, 'Self', 1
--select * from @.child

select AgeGroup,
isnull(ReferralSource, '') as ReferralSource,
sum( case when NoOfReferral is null then 0
else NoOfReferral end
) as NoOfReferral
from ( select 0 as AgeGroup union all select 1 union all
select 2 union all select 3 union all select 4
) as n
left join @.child c
on n.AgeGroup = c.ChildAgeByYear
group by AgeGroup,
ReferralSource
order by AgeGroup,
sum( case when NoOfReferral is null then 0
else NoOfReferral end
) desc

/*
AgeGroup ReferralSource NoOfReferral
-- --
0 0
1 0
2 Self 3
2 Open 1
3 0
4 0
*/

|||Thank you very much
4 your quick response,i will let u now the outcome once i test this
best regards
suis