User Defined Function Problem
-
Hi
I have a table with a field containing days (Monday, Tuesday etc.). I want to sort on this field Monday - Friday.
So, I created a function as below into which I enter (in Query Analyser) the field with the day:
SELECT dbo.fn_FindDayNo([classday]) as DayNo,Classday
from tblclass
I expected to have the number returned into the DayNo column from the function. However, I only get 8 (from the else clause). Somehow it doesn't seem to be reading the day. I have tried creating a new table with just the one field 'ClassDay' and entering a week's worth of days. It still returns the 8??
Has anybody any ideas what mistake I am obviously making.
Thanks
Paul
CREATE FUNCTION fn_FindDayNo (@Dy Varchar)
RETURNS int
AS
BEGIN
DECLARE @FindDayNo int
Declare @d as int
if @Dy = 'Monday'
begin
set @d=1
end
else if @dy = 'Tuesday'
begin
set @d=2
end
else if @dy = 'Wednesday'
begin
set @d=3
end
else if @dy = 'Thursday'
begin
set @d=4
end
else if @dy = 'Friday'
begin
set @d=5
end
else if @dy = 'Saturday'
begin
set @d=6
end
else if @dy = 'Sunday'
begin
set @d=7
end
else
set @d=8
set @FindDayNo = @d
RETURN(@FindDayNo)
END
-
Declare the @Dy variable as Varchar(12) instead of Varchar only.
when you declare @Dy Varchar it takes only the first character of the string assigned to it. For example
Decalre @Dy varchar
set @Dy = 'Monday'
select @Dy
will return only 'M'
hence the condition in your function is always failing.
If the variable is declared with the no of characters specified (@Dy Varchar(12) )it will give you the expected result.
alter FUNCTION fn_FindDayNo (@Dy Varchar(12))
RETURNS int
AS
BEGIN
DECLARE @FindDayNo int
Declare @d as int
if @Dy = 'Monday'
begin
set @d=1
end
else if @dy = 'Tuesday'
begin
set @d=2
end
else if @dy = 'Wednesday'
begin
set @d=3
end
else if @dy = 'Thursday'
begin
set @d=4
end
else if @dy = 'Friday'
begin
set @d=5
end
else if @dy = 'Saturday'
begin
set @d=6
end
else if @dy = 'Sunday'
begin
set @d=7
end
else
set @d=8
set @FindDayNo = @d
RETURN(@FindDayNo)
END
is the solution to your problem
-
This can be achieved in many fewer lines, try this:
declare @days char(100), @classday varchar(10), @dayno int
set @days = ' Monday Tuesday Wednesday Thursday Friday Saturday Sunday'
set @classday = 'Sunday'
set @dayno = charindex(@classday, @days) / 10
select @classday ClassDay, @dayno DayNumber
Obviously, it would need to be tweaked to make it a UDF (ensuring that the @days string is correctly formatted - 'Monday' starts at char 10, 'Tuesday' as char 20 etc etc.
Regards
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Thanks, Padmaja
Absolutely right. Somehow I just couldn't spot it. It now works a treat.
Thanks, Phil
Fascinating code. I'll give it a work through.
Many thanks again to you both
Paul
-
No problem. The other thing you should consider doing is creating a lookup table:
DayNo (int, PK), Day (varchar(10))
and then populating it as follows:
1 Monday
2 Tuesday
........
7 Sunday
Then you can just do a left join to this table and the lookup is there for you in one line.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Why not simply use the numeric form of weekday, and then use DATENAME when outputting it?
-
Thanks, Chris
I am trying to do near-as-possible sort as some of the values in the field don't conform to an actual Day.
Paul
-
Thanks again Phil
This is another good idea. However, the content of the field is not consistent and would not lend itself to this.
Paul
-
Congrats on the 1K mark.
-
Hehe, thanks.
I'm quite happy that post 1000 included actual code for a specific solution, since it symbolize the kind of posts I usually write.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply