Get day of week in SQL Server 2005/2008

Question

If I have a date 01/01/2009, I want to find out what day it was e.g. Monday, Tuesday, etc...

Is there a built-in function for this in SQL Server 2005/2008? Or do I need to use an auxiliary table?

Answer 1

Although this is an old topic it helped me today with SQL-Server2022

A point I would like to add is the language beeing used as the language-setting influences the result.

in my case, the server is set to english but I am working with SSMS in German. So if I am runing the same regequst in SSMS or via the agent I am getting different results.

Hence I recommend to add "set language [your_Language]" first to be sure you get the expected result.

---

Example run on Friday Aug. 16th 2024

SET LANGUAGE german;
Print('german');
Print (DATEPART(dw,GETDATE()) );
--Result: 5


SET LANGUAGE us_english;
Print('us_english');
Print(DATEPART(dw,GETDATE()) );
--Result: 6

Answer 2

If you don't want to depend on @@DATEFIRST or use DATEPART(weekday, DateColumn), just calculate the day of the week yourself.

For Monday based weeks (Europe) simplest is:

SELECT DATEDIFF(day, '17530101', DateColumn) % 7 + 1 AS MondayBasedDay

For Sunday based weeks (America) use:

SELECT DATEDIFF(day, '17530107', DateColumn) % 7 + 1 AS SundayBasedDay

This returns the weekday number (1 to 7) ever since January 1st respectively 7th, 1753.

Answer 3

You may find this version useful for returning a shortened weekday name in one line.

-- Test DATA
select @@datefirst
create table #test (datum datetime)
insert #test values ('2013-01-01')
insert #test values ('2013-01-02')
insert #test values ('2013-01-03')
insert #test values ('2013-01-04')
insert #test values ('2013-01-05')
insert #test values ('2013-01-06')
insert #test values ('2013-01-07')
insert #test values ('2013-01-08')
-- Test DATA

select  Substring('Sun,Mon,Tue,Wed,Thu,Fri,Sat,Sun,Mon,Tue,Wed,Thu,Fri,Sat',
        (DATEPART(WEEKDAY,datum)+@@datefirst-1)*4+1,3),Datum
        from #test 

Answer 4

In addition all with above answers, dw stands for Week Day

SELECT DATENAME(WEEKDAY,GETDATE()) AS WeekDay

or

SELECT DATENAME(W,GETDATE()) AS WeekDay

Answer 5

Even though SQLMenace's answer has been accepted, there is one important SET option you should be aware of

SET DATEFIRST

DATENAME will return correct date name but not the same DATEPART value if the first day of week has been changed as illustrated below.

declare @DefaultDateFirst int
set @DefaultDateFirst = @@datefirst
--; 7 First day of week is "Sunday" by default
select  [@DefaultDateFirst] = @DefaultDateFirst 
    
set datefirst @DefaultDateFirst
select datename(dw,getdate()) -- Saturday
select datepart(dw,getdate()) -- 7

--; Set the first day of week to * TUESDAY * 
--; (some people start their week on Tuesdays...)
set datefirst 2
select datename(dw,getdate()) -- Saturday
--; Returns 5 because Saturday is the 5th day since Tuesday.
--; Tue 1, Wed 2, Th 3, Fri 4, Sat 5
select datepart(dw,getdate()) -- 5 <-- It's not 7!
set datefirst @DefaultDateFirst

Answer 6

this is a working copy of my code check it, how to retrive day name from date in sql

CREATE Procedure [dbo].[proc_GetProjectDeploymentTimeSheetData] 
@FromDate date,
@ToDate date

As 
Begin
select p.ProjectName + ' ( ' + st.Time +' '+'-'+' '+et.Time +' )' as ProjectDeatils,
datename(dw,pts.StartDate) as 'Day'
from 
ProjectTimeSheet pts 
join Projects p on pts.ProjectID=p.ID 
join Timing st on pts.StartTimingId=st.Id
join Timing et on pts.EndTimingId=et.Id
where pts.StartDate >= @FromDate
and pts.StartDate <= @ToDate
END

Answer 7

To get a deterministic value for the day of week for a given date you could use a combination of DATEPART() and @@datefirst. Otherwise your dependent on the settings on the server.

Check out the following site for a better solution: MS SQL: Day of Week

The day of week will then be in the range 0 to 6, where 0 is Sunday, 1 is Monday, etc. Then you can use a simple case statement to return the correct weekday name.

Answer 8

You can use DATEPART(dw, GETDATE()) but be aware that the result will rely on SQL server setting @@DATEFIRST value which is the first day of week setting (In Europe default value 7 which is Sunday).

If you want to change the first day of week to another value, you could use SET DATEFIRST but this may affect everywhere in your query session which you do not want.

Alternative way is to explicitly specify the first day of week value as parameter and avoid depending on @@DATEFIRST setting. You can use the following formula to achieve that when need it:

(DATEPART(dw, GETDATE()) + @@DATEFIRST + 6 - @WeekStartDay) % 7 + 1

where @WeekStartDay is the first day of the week you want for your system (from 1 to 7 which means from Monday to Sunday).

I have wrapped it into below function so we can reuse it easily:

CREATE FUNCTION [dbo].[GetDayInWeek](@InputDateTime DATETIME, @WeekStartDay INT)
RETURNS INT
AS
BEGIN
    --Note: @WeekStartDay is number from [1 - 7] which is from Monday to Sunday
    RETURN (DATEPART(dw, @InputDateTime) + @@DATEFIRST + 6 - @WeekStartDay) % 7 + 1 
END

Example usage: GetDayInWeek('2019-02-04 00:00:00', 1)

It is equivalent to following (but independent to SQL server DATEFIRST setting):

SET DATEFIRST 1
DATEPART(dw, '2019-02-04 00:00:00')

Answer 9

Use DATENAME or DATEPART:

SELECT DATENAME(dw,GETDATE()) -- Friday
SELECT DATEPART(dw,GETDATE()) -- 6

Answer 10

With SQL Server 2012 and onward you can use the FORMAT function

SELECT FORMAT(GETDATE(), 'dddd')

Answer 11

SELECT  CASE DATEPART(WEEKDAY,GETDATE())  
    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

Answer 12

EUROPE:

declare @d datetime;
set @d=getdate();
set @dow=((datepart(dw,@d) + @@DATEFIRST-2) % 7+1);