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

The day of the week can be retrieved in SQL Server by using the DatePart function. The value returned by function is between 1 (Sunday) and 7 (Saturday). To convert this to a string representing the day of the week, use a CASE statement.

Method 1:

Create function running following script:
CREATE FUNCTION dbo.udf_DayOfWeek(@dtDate DATETIME)
RETURNS VARCHAR(10)
AS
BEGIN
DECLARE
@rtDayofWeek VARCHAR(10)
SELECT @rtDayofWeek = CASE DATEPART(weekday,@dtDate)
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

Call this function like this:
SELECT dbo.udf_DayOfWeek(GETDATE()) AS DayOfWeek
ResultSet:
DayOfWeek
———-
Monday

Method 2: (This is update from comments I received below)
SELECT DATENAME(dw, GETDATE())

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

, , ,
Previous Post
SQLAuthority News – FQL – Facebook Query Language
Next Post
SQL SERVER – UDF – Function to Get Previous And Next Work Day – Exclude Saturday and Sunday

Related Posts

56 Comments. Leave new

  • Hi I have created computed col with used following function for GET WEEK OF THE DATE but when create a index on that it says this is Non deterministic function, please help me on that how can i convert it into deterministic

    create function dev_log.F_ISO_WEEK_OF_YEAR
    (
    @Date datetime
    )
    returns int
    as
    /*
    Function F_ISO_WEEK_OF_YEAR returns the
    ISO 8601 week of the year for the date passed.
    */
    begin

    declare @WeekOfYear int

    select
    — Compute week of year as (days since start of year/7)+1
    — Division by 7 gives whole weeks since start of year.
    — Adding 1 starts week number at 1, instead of zero.
    @WeekOfYear =
    (datediff(dd,
    — Case finds start of year
    case
    when NextYrStart <= @date
    then NextYrStart
    when CurrYrStart <= @date
    then CurrYrStart
    else PriorYrStart
    end,@date)/7)+1
    from
    (
    select
    — First day of first week of prior year
    PriorYrStart =
    dateadd(dd,(datediff(dd,-53690,dateadd(yy,-1,aa.Jan4))/7)*7,-53690),
    — First day of first week of current year
    CurrYrStart =
    dateadd(dd,(datediff(dd,-53690,aa.Jan4)/7)*7,-53690),
    — First day of first week of next year
    NextYrStart =
    dateadd(dd,(datediff(dd,-53690,dateadd(yy,1,aa.Jan4))/7)*7,-53690)
    from
    (
    select
    –Find Jan 4 for the year of the input date
    Jan4 =
    dateadd(dd,3,dateadd(yy,datediff(yy,0,@date),0))
    ) aa
    ) a

    return @WeekOfYear

    end
    go

    Reply
  • i want to display week days dates of the month from created date .

    E: if i select radio button as week days on 11/11/2011

    it should calculate nov months week dates

    Reply
  • if i select calender from to to date it should select only week end dates

    Reply
  • nice blog thanks

    Reply
  • SELECT DATENAME(weekday, ‘1988/08/31’) will do

    Reply
  • It doesn’t have to be anywhere as near as complicated as that try this:

    TO_CHAR(column_name, ‘Day dd-mon-yy’)

    you can include this in other scripts.

    I used this in a report i run daily to let me know when invoices need to be authorised. This script excludes weekends in a CASE:

    TO_CHAR (( CASE
    WHEN TO_CHAR(ps.run_date,’DY’) = ‘MON’
    THEN TRUNC(ps.run_date)-3
    ELSE TRUNC(ps.run_date)-1
    END), ‘Day dd-mon-yy’) AS AUTHORISE_BY

    Oh yes, I’m English, so i spell authorised in an English way.

    Reply
  • I want to those record for the start date are from Monday to Sunday in sql query

    Thanks

    Reply
  • Hi,
    I want to create one attendance report so,in that i want two columns one is “Total Working days” of the week and second column is “number of days present”. please help me for the same.

    Reply
  • Pinal,
    Thank you for your blogposts. They have helped me a lot when I am stuck in SQL.
    Please keep up the good work.

    Reply
  • bhagatkhetwalhagat
    February 19, 2014 12:33 pm

    Hi Pinal,
    i need your help on the below reuirement.
    i have a table called attendance having column EmpId,Department,date,status
    i want to have a weekly headcount report where i can get the data in the desied format like

    Department | W1 |W2|W3|W4|W5|W6|W7|W8 and so on…
    _____________________________________________
    Dep1 20 23 25 26 27 21 and so on
    Dep2 76 77 63 76 72 27 and so on
    Dep3 75 52 46 57 68 53 76 and so on..

    W1,W2 is nothing but week number

    Actully report is to get emp head count weekwise..

    Can u suggest me somthing to sortout this issue..

    Reply

Leave a Reply

Menu