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
----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,@dtDate)+@@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 0 THEN 'Saturday'
END
RETURN (@rtDayofWeek)
END
GO
SELECT dbo.udf_dayofweek(GETDATE())

Reference : Pinal Dave (https://blog.sqlauthority.com) , Nanda Kumar

Solarwinds
, ,
Previous Post
SQLAuthority News – SQL SERVER 2008 – New Logo
Next Post
SQLAuthority News – Download – Windows Server 2008 w/ SQL Server 2005

Related Posts

2 Comments. Leave new

  • Brijendra Pandey
    June 26, 2008 6:03 pm

    Hi Prashant Pandey,

    How can we restore data from log files (.ldf) in sql 2005

    Thanks & Regards

    BRijendra Pandey

    Reply
  • Satish Vellanki
    June 29, 2016 4:48 pm

    DECLARE @week varchar(15)
    DECLARE @weekday int
    Set @weekday=(DATEPART(dw,GETDATE()))
    SELECT @week=Case @weekday WHEN 1 Then ‘SUNDAY’
    WHEN 2 Then ‘MONDAY’
    WHEN 3Then ‘TUESDAY’
    WHEN 4 Then ‘WEDNESDAY’
    WHEN 5 Then ‘THURSDAY’
    WHEN 6 Then ‘FRIDAY’
    ELSE ‘SATURDAY’
    END
    SELECT @week

    Reply

Leave a Reply

Menu