Substring function not comparing the output

Question

ALTER FUNCTION WEEKDAYS(@DAY VARCHAR)
RETURNS INT
AS BEGIN
    IF(@DAY IS NULL)
    RETURN 0

        DECLARE @OUTPUT INT

        SELECT @OUTPUT=
            CASE WHEN SUBSTRING(@DAY,1,1) = 'M' THEN 1
                 WHEN SUBSTRING(@DAY,1,2) = 'TU' THEN 2
                 WHEN SUBSTRING(@DAY,1,1) = 'W' THEN 3
                 WHEN SUBSTRING(@DAY,1,2) = 'TH' THEN 4
                 WHEN SUBSTRING(@DAY,1,1) = 'F' THEN 5
                 WHEN SUBSTRING(@DAY,1,2) = 'SA' THEN 6
                 WHEN SUBSTRING(@DAY,1,2) = 'SU' THEN 7
            ELSE
                99 END
        RETURN @OUTPUT
    END

I have this function with me. In case of monday, wednesday and friday the output that the function is printing is correct. but in other cases it is printing the output from else condition.

Your DAY parameter is declared as varchar - which implicitly means varchar(1). Your code assumes varchar(2). There is an important lesson to learn here. I'll take this a step further - "weekdays" are not always "work days". Be careful what you assume. — SMor, Jan 24 '19 at 12:26
And why would you need to use substring on your parameter? Someone passing an invalid value should receive an invalid return value or an error. Don't attempt to hide source coding errors. And don't encourage lazy developers by accomodating their laziness. — SMor, Jan 24 '19 at 12:28

score 3 · Answer 1 · answered Jan 24 '19 at 12:00

Always declare you length, scale and precision... Not declaring your length will lead to unexpected behaviour; just as you have here.

ALTER FUNCTION WEEKDAYS(@DAY VARCHAR)

is the same as:

ALTER FUNCTION WEEKDAYS(@DAY VARCHAR(1))

As @DAY has a length of 1 it can never have the value 'TU', as that is 2 characters; double to maximum length of @DAY. Define an appropriate length for your varchar parameter and the problem goes away.

On a totally different note though, why are you using a UDF to get the weekday number why not DATEPART(WEEKDAY, {Date})?

Suraj Kumar · Answer 2 · 2019-01-24T12:14:48.767

0

You can also get this without function using DATEPART() function. But to correct your function you can try the following.

ALTER FUNCTION WEEKDAYS(@DAY VARCHAR(10))
RETURNS INT
AS BEGIN
    IF(@DAY IS NULL)
    RETURN 0

        DECLARE @OUTPUT INT

        SELECT @OUTPUT=
            CASE WHEN SUBSTRING(@DAY,1,1) = 'M' THEN 1
                 WHEN SUBSTRING(@DAY,1,2) = 'TU' THEN 2
                 WHEN SUBSTRING(@DAY,1,1) = 'W' THEN 3
                 WHEN SUBSTRING(@DAY,1,2) = 'TH' THEN 4
                 WHEN SUBSTRING(@DAY,1,1) = 'F' THEN 5
                 WHEN SUBSTRING(@DAY,1,2) = 'SA' THEN 6
                 WHEN SUBSTRING(@DAY,1,2) = 'SU' THEN 7
            ELSE
                99 END
        RETURN @OUTPUT
    END

SELECT dbo.WEEKDAYS('SUNDAY') as SUNDAY
SELECT dbo.WEEKDAYS('MONDAY') as MONDAY
SELECT dbo.WEEKDAYS('TUESDAY') as TUESDAY
SELECT dbo.WEEKDAYS('WEDNESDAY') as WEDNESDAY
SELECT dbo.WEEKDAYS('THURSDAY') as THURSDAY
SELECT dbo.WEEKDAYS('FRIDAY') as FRIDAY
SELECT dbo.WEEKDAYS('SATURDAY') as SATURDAY

The output is as shown below

edited Jan 24 '19 at 12:14

answered Jan 24 '19 at 12:09

Suraj Kumar

5,547
8
20
42

Th**ur**sday. ;) – Thom A Jan 24 '19 at 12:10
@Larnu How it is giving 4 as per the condition. – Suraj Kumar Jan 24 '19 at 12:11
Th***ur***sday, not "Thrusday". – Thom A Jan 24 '19 at 12:12
@Larnu corrected typo. But it was not the technical issue as here only two characters are checking and comparing. – Suraj Kumar Jan 24 '19 at 12:16

Substring function not comparing the output

2 Answers2

Linked