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)

SQL DateTime, SQL Function, SQL Scripts, SQL Utility
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

  • Can i get spanish names whit DATENAME ?

    Reply
    • Yes Only if the default language of the server is Spanish

      Reply
      • my question is i putup a any date of future and want to make calendre according to this way of any flight

        my table is like

        flightno sun mon tues wed thurs friday saturday
        1001 y y n n n y n
        1002 n y n y y n y

  • 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

    Reply
  • 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

    Reply
  • Time and Date only

    SELECT CONVERT(VARCHAR(12), GETDATE(), 106)
    SELECT CONVERT(VARCHAR(8), GETDATE(), 108)

    Reply
  • @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

    Reply
  • 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

    Reply
  • 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.

    Reply
  • 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

    Reply
  • 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

    Reply
  • 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.

    Reply
  • how to display weeknumbers and and its correponding year?

    Reply
  • 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

    Reply
  • 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

    Reply
  • Aanjanneyulu
    July 26, 2011 11:06 am

    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.

    Reply
  • Aanjanneyulu
    July 26, 2011 11:09 am

    Hi,

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

    Thanks in advance.

    Reply
  • 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

    Reply
  • SELECT DATENAME(wk,GETDATE())-1

    ans: 32

    thanks

    Reply
  • 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

    Reply
  • To get a deterministic value for the day of week for a given date you could use a combination of datepart and @@firstday. Otherwise your dependent on the settings on the server.

    Check out the following site for a better solution:

    Reply
  • 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…

    Reply

Leave a ReplyCancel reply

Exit mobile version