SQL SERVER – SQL SERVER – UDF – Get the Day of the Week Function – Part 3

Datetime functions and stored procedures always interests me. Nanda Kumar has suggested modification to previous written article about SQL SERVER – SQL SERVER – UDF – Get the Day of the Week Function – Part 2. He has improved on UDF.

CREATE FUNCTION dbo.udf_DayOfWeek(@dtDate DATETIME)
RETURNS VARCHAR(10)
AS
BEGIN
DECLARE @rtDayofWeek VARCHAR(10)
DECLARE @weekDay INT
----Adding @@DATEFIRST gives 1 for Sunday to 6 for Friday and 0 for Saturday, whatever the DATEFIRST setting is
SET @weekDay=((DATEPART(dw,@dtDate)+@@DATEFIRST)%7)
SELECT @rtDayofWeek = CASE @weekDay
WHEN 1 THEN 'Sunday'
WHEN 2 THEN 'Monday'
WHEN 3 THEN 'Tuesday'
WHEN 4 THEN 'Wednesday'
WHEN 5 THEN 'Thursday'
WHEN 6 THEN 'Friday'
WHEN 0 THEN 'Saturday'
END
RETURN (@rtDayofWeek)
END
GO
SELECT dbo.udf_dayofweek(GETDATE())

Test Any Function to Get the Day of the Week With SET DATEFIRST

Day of week code has one trap that catches almost everyone: the number from DATEPART(dw, ...) depends on the @@DATEFIRST setting. In US English, Sunday is day 1. With British English, or after SET DATEFIRST 1, Monday is day 1. The same date can return a different number on another server, or even in another session.

The function above adds @@DATEFIRST to cancel out that setting, which is the right idea. Before you use it in production, test it under more than one setting:

• Run SET DATEFIRST 1 and call the function for seven days in a row.
• Run SET DATEFIRST 7 and do the same.
• Compare both lists with a calendar.

Watch for NULL in that test. The first version of this function subtracted 7 inside the brackets. In T-SQL, the % operator keeps the sign of the number on its left, so with SET DATEFIRST 1 the value dropped below zero for Monday to Friday, the remainder was negative and the CASE returned NULL. The code above now uses (DATEPART(dw, @dtDate) + @@DATEFIRST) % 7. It gives the same 0 to 6 values, with 1 for Sunday and 0 for Saturday, and it can never go below zero. Run the seven day test again after any change to confirm it.

Also remember that SET LANGUAGE changes @@DATEFIRST as a side effect. A login with a different default language can change the result without anyone ever running SET DATEFIRST.

If all you need is the name, DATENAME(weekday, @dtDate) returns it directly. The name comes back in the language of the session, which is fine for reports in one language but something to remember if your users work in several. Whichever way you choose, keep the test script next to the function, so the next change gets the same check.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.