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 (http://blog.SQLAuthority.com)

About these ads

58 thoughts on “SQL SERVER – UDF – Get the Day of the Week Function

  1. Why not use the datename() function instead – it’s already built in to SQL Server?
    IE – SELECT DATENAME(dw, GETDATE())

  2. CREATE FUNCTION dbo.udf_DayOfWeek(@dtDate DATETIME)
    RETURNS VARCHAR(10)
    AS
    BEGIN
    DECLARE @rtDayofWeek VARCHAR(10)
    SELECT @rtDayofWeek = CASE DATEPART(weekday,@dtDate)
    WHEN 0 THEN ‘Sunday’
    WHEN 1 THEN ‘Monday’
    WHEN 2 THEN ‘Tuesday’
    WHEN 3 THEN ‘Wednesday’
    WHEN 4 THEN ‘Thursday’
    WHEN 5 THEN ‘Friday’
    WHEN 6 THEN ‘Saturday’
    END
    RETURN (@rtDayofWeek)
    END
    GO

    for right result we have to change this to

    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

  3. Why would you even keep the first method up on the site when there is a built in function that performs the same job? It doesn’t seem like a best practice to rewrite working functions on your SQL Server.

  4. Steve,

    This is fun article and never claimed as best practice. I kept the first method up cause I think it is still interesting to read it.

    Regards,
    Pinal Dave (SQLAuthority.com)

  5. The tricky thing here is that Sunday does not always equal 1. The values returned by DATEPART(dw) will change based on the setting of @@DATEFIRST (which indicates the first day of the week). You will notice this problem most readily in locales where Monday (or some other day) is the first day of the week. To test, invoke “SET DATEFIRST 1″ and try the provided function.

    To get past this issue, you can try the following:

    (DATEPART(dw, @SomeDate) + @@DATEFIRST) % 7

    When you do this, you’ll see that a Wednesday is 4, regardless of the DATEFIRST setting.

  6. dbo.udf_PreviousThursday
    =====================

    Here is a useful UDF which takes a datetime value as input and returns as datetime output the previous Thursday:

    ALTER FUNCTION dbo.udf_PreviousThursday(@dtDate DATETIME)
    RETURNS DATETIME
    AS
    BEGIN

    DECLARE @dt_PreviousThursday DATETIME
    DECLARE @i_SubtractBy int –the number of days you must subtract from the input date to get the previous Thursday

    SELECT @i_SubtractBy =
    CASE DATEPART(weekday,@dtDate)
    WHEN 1 THEN -4 –(i.e when Monday subtract 4 days to get the previous Thursday)
    WHEN 2 THEN -5 –(i.e when Tuesday subtract 5 days to get the previous Thursday)
    WHEN 3 THEN -6 –(i.e when Wednesday subtract 6 days to get the previous Thursday)
    WHEN 4 THEN -7 –(i.e when Thursday subtract 7 days to get the previous Thursday) should this be zero??
    WHEN 5 THEN -1 –etc.
    WHEN 6 THEN -2 –etc.
    WHEN 7 THEN -3 –etc.
    END

    SELECT @dt_PreviousThursday = DATEADD(day, @i_SubtractBy, @dtDate)
    RETURN @dt_PreviousThursday
    END
    GO

    –Sample execute
    SET DATEFIRST 1–set Monday i.e. not Sunday to be the first day of the week
    SELECT dbo.udf_PreviousThursday(getdate())
    SELECT dbo.udf_PreviousThursday(‘2007-01-12 13:52:09.043′)

  7. In reply to Smita’s question, one of the existing datetime conversion formats can normally be relied upon for the conversion of datetime to string
    e.g.
    SELECT CONVERT(VARCHAR(100), GETDATE(), 106)

    A list of common conversion styles is here:
    http://technet.microsoft.com/en-us/library/aa237895(SQL.80).aspx

    Of course, if you want to do something quite specific, you will have to use DATEPARTs to extract the relevant parts of the date, and concatenate these together. You need to convert all the parts to varchar prior to concatenation or you’ll get an error.

    As a useful tip also, if you are getting 1/12/2007 and you want to get 01/12/2007 you need to use the right function like so:

    right( ’00’ + Cast(datepart(dd, getdate()) as varchar), 2)

  8. Hi pinal,

    I need to know, How can I count the working days excluding sunday and saturday.
    like if i need to count 7 working days..and today is monday so my seventh working days should be tuesday and i have to display the tuesday instead of the date fallin on tuesday.
    Any help will be appreciated .

    Regards
    Vivek

  9. @vivek

    Give me some more details, like are you trying to retrieve data from a table, if yes then give the table structure what are the columns … that will be help me a little bit.

    Thanks.

  10. This function will let you correct day of the week considering the fact that DateFirst need not be always equal to 1

    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
    @weekDay = Select ((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

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

  12. Dear Sir,

    I need your help. I want to get the starting date and ending date for input week number. I am using sql server 2005.

    Please do reply me soon.

    Byyee and have a nice day.

  13. Hi,

    Please find this code..it might helps you.

    –it calculates based upon current years Week#
    DECLARE @week INT,
    @weekdt DATETIME
    SELECT @week = 40 –For Current Week
    –get First day of current year
    SELECT @weekdt = DATEADD(wk, @week, DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))
    SELECT @week AS Week#,
    DATEADD(DD, 1 – DATEPART(DW, @weekdt), @weekdt) As WeekStartDate,
    DATEADD(DD, 6, DATEADD(DD, 1 – DATEPART(DW, @weekdt), @weekdt)) As WeekEndDate

  14. Sebastian,

    This may help you

    ;WITH CTE AS
    (
    SELECT
    1 AS ID,
    SUBSTRING(days,0,CHARINDEX(‘,’,days)) AS Days,
    SUBSTRING(days,CHARINDEX(‘,’,days)+1,LEN(days))RemainingStr,
    2%8 AS DayID
    FROM
    sys.sysLanguages
    WHERE
    alias = ‘spanish’

    UNION ALL
    SELECT
    ID+1 AS ID ,
    CASE WHEN CHARINDEX(‘,’,RemainingStr) > 0 THEN SUBSTRING(RemainingStr,0,CHARINDEX(‘,’,RemainingStr))ELSE RemainingStr END AS Days,
    SUBSTRING(RemainingStr,CHARINDEX(‘,’,RemainingStr)+1,LEN(RemainingStr))RemainingStr,
    (DayID+1)%8 AS DayID
    FROM
    CTE
    WHERE
    ID < 7

    )

    SELECT Days,DAyID FROM CTE

  15. HI, great post…

    But, it’s possible with only data result “15-07-2007″ (called date) you know what is the first day of the week?

    For example I want this result:

    datepart(month, date) + ‘ ‘ + datepart(week, date) + ‘ ‘ + “First day of the week”

    How I catch “First day of the week” ???

    thanks in advance

  16. @Nagarajan,

    Note that all your TIME and DATEs are just VARCHARs
    You should do this formation in the front end application

    or

    If you use SQL Server 2008, make use of new datatypes Time and Date

  17. Hi All,

    I want to show the first date of the week, example today date 25-02-2010, i want to 21-02-2010.

    Inputs given year,month,week.(example:2010,02,4)

    i want to output of this week first date

    Gururajan.K

  18. Hi Pinal,
    I want the start date of week and the end date of week.A date will be passed as a parameter,based on the week number of that date I need the start & end date of that week.

  19. Hi,

    Can anyone help me to get the first “Sunday” in (Integer) of the given date… which will return the day of the first sunday

    Example:

    Date: ’05/22/2010′

    Then I want to get the day (integer) of the first sunday of this date ’05/22/2010′

    It should Return 2

    Because the first sunday in the month of May 2010 is 2

  20. Continued…

    Because I want to get the Week1 to Week5

    In my example there are 5 sundays in the Month of May 2010

    Week 1 – date will be May 1 to May 2
    Week 2 – date will be May 3 to May 9
    Week 3 – date will be May 10 to May 16
    Week 4 – date will be May 17 to May 23
    Week 5 – date will be May 24 to May 30
    Week 6 – date will be May 31

    The only thing i want to know is the first sunday of the given date in integer in this case…

    I want to get the 2,
    Because the first sunday in the month of May 2010 is 2

  21. just want to ask…

    how to parse the each date in a year into MS SQL database? if i must store those date in an array?? if so, how can i parse them ??

    hope to hear some comments…..

    really need.

  22. Hi, sorry I’m new on SQL codification … could you please tell me how can I include this function in a SELECT codification?

    Regards.
    Alejandro

  23. Hi Pinal,
    i have day of week , on the basis of day of week i want to find the next particular day (date)

    for Example i have 3 =Tuesday ,i want to calculate next tuesday date on basis of current current date

  24. Hi,

    I want Monday – Sunday week numbers.
    I have to group by this week number and i have to get count of records in between that week.

    Thanks in advance.

  25. Hi,

    I want a method like DATEPART(WK,getdate()) , but it should take from monday – sunday.

    Thanks in advance.

  26. HI Everyone,

    Can please help me any one on this issue:

    when i’m runing this query:

    SELECT DATENAME(wk,GETDATE())
    ans:33

    but,with this :
    SELECT DATENAME(wk,GETDATE()-1)
    ans:33

    can anyone plz tel why it is this ,actualy i think output should be 32 when we do -1.

    thanks in advance

  27. I want to subtract from StartDate from current Date
    from Table

    select Hours = datediff(hh,0,DtDiff,StartDate) , Minutes = datepart(minute,DtDiff)

    (select DtDiff = convert(datetime,’20110715 10:5:45′)- convert(DateTime,GetDate())
    )a
    from
    Table Name

    this is my query plz correction on it

  28. Hi,

    I have a question that, I want to see a Table design in SQL Server. So which query I want to write to see the the design.

    Thanks and Regards,
    Madhu…

  29. 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

  30. 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

  31. 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.

  32. 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.

  33. Pingback: SQL SERVER – Weekly Series – Memory Lane – #039 | Journey to SQL Authority with Pinal Dave

  34. 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..

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