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())

Reference : Pinal Dave (http://blog.SQLAuthority.com), Mihir Popat

10 thoughts on “SQL SERVER – SQL SERVER – UDF – Get the Day of the Week Function – Part 2

  1. I’m still a little confused as to why you would want to go through the trouble of calling a UDF with 18 to 20 lines of code when the SQL Server development team has already done it for you.

    SELECT DATENAME(dw, GETDATE())

    I do understand the fun that can be had with trying to solve complex problems with T-SQL, but this is not one of those complex problems. It’s built in to the system for anyone to use when ever they want.

    Like

  2. Hi Pinal,

    I’m also surprised to see effort you’ve put to retrive Day of week for a given date. DATENAME function can perform this task pretty well.

    I’m sure you must be aware of this function but you might have face some issue with it. Could you please share those issues wit DATENAME function?

    Thanks & Regards,
    Sudev Gandhi
    [http://sudev.blogspot.com

    Like

  3. Hi Pinal,

    Actually the function u posted is returning ‘NULL’ for Saturdays and works for rest all days. And you didn’t use the given argument inside the funciton instead you used getdate(). this also returns error. The following is working…

    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())

    I’m happy to reply this post as Novice DBA

    Nandha

    Like

  4. Pingback: SQL SERVER - SQL SERVER - UDF - Get the Day of the Week Function - Part 3 Journey to SQL Authority with Pinal Dave

  5. Do you know how to get the actual weekday name on a date function.

    i.e.

    Mon
    Tues
    Wed

    I think it’s

    SELECT DATENAME(dw, GETDATE()) but it’s not giving me the actual name of the day of the week?

    Like

  6. Use B
    select datename(weekday,date) as day,sum(amount)as sales
    from afa
    where afatypex in (63)
    and date >= ‘7/1/2008′
    and date <'7/1/2009'
    Group by date
    Order by date asc

    This simple query gives me the sales amounts by weekday Monday thru Sunday which is great but what I'm wanting to do is strip out days Sun thru Wednesday for a grand total for the entire lenth of data

    Example:

    Monday 68874.0000
    Tuesday 91701.0000
    Wednesday 223098.0000
    Thursday 74457.0000
    Friday 114947.0000
    Saturday 252631.0000
    Sunday 47100.0000
    Monday 68874.0000
    Tuesday 91701.0000
    Wednesday 223098.0000
    Thursday 74457.0000
    Friday 114947.0000
    Saturday 252631.0000
    Sunday 47100.0000

    maybe I should use the datepart(weekday,date) and then in the where clause say date like '%7,1,2,3,4%' or date in (7,1,2,3,4) but it won't let me do that?

    Anyone have a suggestions?

    Like

  7. Simply write.
    SELECT DATENAME(WEEKDAY, GETDATE())

    WHY ALL THIS?:

    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())

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s