Wednesday, October 14, 2009

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


I have written article about SQL SERVER – UDF – Get the Day of the Week Function. I have received good modified script from reader Mihir Popat has suggested another code where Sunday does not have to be necessary the first day of the week.

CREATE FUNCTION
dbo.udf_DayOfWeek(@dtDate DATETIME)
RETURNS VARCHAR(10)
AS
BEGIN
DECLARE
@rtDayofWeek VARCHAR(10)
DECLARE @weekDay INT
-- Here I have subtracted 7 For keeping Sunday as the First day
-- like wise for Monday we need to subtract 2 and so on
SET @weekDay = ((DATEPART(dw,GETDATE())+@@DATEFIRST-7)%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 7 THEN 'Saturday'
END
RETURN
(@rtDayofWeek)
END
GO
SELECT dbo.udf_DayOfWeek(GETDATE())


Source:YM vannetsoft

Skype:vancvcafe


No comments:

Post a Comment