I have a problem creating a function, can't resolve the issue

Question

I have a problem creating a function in T-SQL.

USE DB_ASSIG1
BEGIN
    CREATE FUNCTION myFunction (@date DATETIME)
        RETURNS INT
            AS
            BEGIN
                DECLARE @day INT

                CASE SELECT DATENAME(dw, @date)
                    WHEN 'Monday'
                        THEN SET @day = 1
                    WHEN 'Tuesday'
                        THEN SET @day = 2
                    WHEN 'Wednesday'
                        THEN SET @day = 3
                    WHEN 'Thursday'
                        THEN SET @day = 4
                    WHEN 'Friday'
                        THEN SET @day = 5
                    WHEN 'Saturday'
                        THEN SET @day = 6
                    WHEN 'Sunday'
                        THEN SET @day = 7
                    END

                    RETURN (@day)
            END
END

Can't resolve it please help, by the way i'm kinda newish to T-SQL.

This is the error i get:

Msg 156, Level 15, State 1, Line 3 Incorrect syntax near the keyword 'FUNCTION'.

Msg 156, Level 15, State 1, Line 9 Incorrect syntax near the keyword 'CASE'.

Msg 137, Level 15, State 2, Line 9 Must declare the scalar variable "@date".

Msg 156, Level 15, State 1, Line 12 Incorrect syntax near the keyword 'WHEN'.

Msg 156, Level 15, State 1, Line 14 Incorrect syntax near the keyword 'WHEN'.

Msg 156, Level 15, State 1, Line 16 Incorrect syntax near the keyword 'WHEN'.

Msg 156, Level 15, State 1, Line 18 Incorrect syntax near the keyword 'WHEN'.

Msg 156, Level 15, State 1, Line 20 Incorrect syntax near the keyword 'WHEN'.

Msg 156, Level 15, State 1, Line 22 Incorrect syntax near the keyword 'WHEN'.

Msg 137, Level 15, State 1, Line 23 Must declare the scalar variable "@day".

Msg 137, Level 15, State 2, Line 26 Must declare the scalar variable "@day".

Completion time: 2020-05-22T22:54:38.4427014+02:00

Answer 1

Create function must be the first statement in a batch. Go terminates a batch, e.g. the one containing the use statement.

The function can be made independent of DateFirst and Language thusly:

USE DB_ASSIG1

go -- Required batch separator.

create function dbo.someoneelsesCode( @Date as Date )
  -- Returns the day-of-the-week for the date with Monday = 1 through Sunday = 7.
  -- It is unaffected by the setting of   DateFirst   or   Language .
  returns Int
  begin
  return ( @@DateFirst + DatePart( weekday, @Date ) - 2 ) % 7 + 1;
  end;

Answer 2

Just another idea...

 CREATE OR ALTER FUNCTION dbo.NumericDOW
        (@pDate DATETIME)
RETURNS TINYINT AS
  BEGIN --==== Note that -53690 is the 1st of January, 1753, which is a Monday
        RETURN DATEDIFF(dd,-53690,@pDate)%7+1
    END
;

While I certainly understand the idea of abstracting such formula's for ease of use, I personally wouldn't use this in a Scalar Function because it will automatically be about 7 times slower than inline code. If you can't remember the number -53690, then use "0", which is the 1st of January, 1900, which is also a Monday and a whole lot easier to remember. The formula would look like this...

DATEDIFF(dd,0,@pDate)%7+1

As a bit of an educational sidebar, both -53690 and 0 are "Date Serial Numbers" based on the 1st of January, 1900 being "day zero". "Date Serial Numbers" are a powerful tool for things like this and for generating sequences of dates.