SQL View using Function Returns only the First Char

[Luther1978]

I'm in the process of writing a Function to be used in a view, After many atempts I simplified the fucntio to this:

CREATE FUNCTION FindWeekNo
(@StartDate as datetime)
RETURNS char
AS
BEGIN
DECLARE @temp as char
SET @TEmp = 'Hello'
RETURN @Temp
END

My problem is whatever function I use in my view I only get the first character Returned. In the above case "H"

Other Functions I have tried where I getting only the first number of the year returned ie. 2

Many Thanks in Advance

Martin

 

[gmmastros]

You should specify how many characters your char field will hold.

Change...

CREATE FUNCTION FindWeekNo 
(@StartDate as datetime)  
RETURNS char

To...

CREATE FUNCTION FindWeekNo 
(@StartDate as datetime)  
RETURNS char[red](200)[/red]

I used 200 as an example.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Luther1978]

Hmm, I thought I'd tried that, can't of done. Its worked, I feel dumb now.

Thank you anyhow.

 

[gmmastros]

Don't worry about it. We've all done 'dumb' things. Consider it a leason learned.

By the way, this is my all time SQL Server pet peeve. Whenever you use a char field, you should specify the size. This also holds true for nchar (the unicode equivalent of char), varchar, and nvarchar.

Similarly, when you use Decimal and/or numeric, you should specify the precision and scale. This should be a habit, something you always do. It will help keep you out of trouble.

I'm glad you got things working out.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom