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?

If you have a table containing lookups for date portions, you have generally done something wrong. SQL Server's date functions are many and robust so any data you need to extract from a date can be readily done on a datetime column. — Benjamin Autin, Jul 10 '09 at 17:56
auxiliary tables are very useful for date calculations, it is not unusual to have a calendar auxiliary table... — , Jul 10 '09 at 17:58
"Useful for date calculations" is highly dubious. Most date calculations can be handled without any kind of auxiliary table and will perform better, too. In some cases, a plain Numbers table will do the job--no need for a table with actual dates in it. The only reason I have seen that an actual calendar table needed is when the rules for which days are work days and which days are not are very complicated. What I HAVE seen far too often is people using date tables because they don't know how to do it any other way. Then they have to populate the date table every so often. Silly. — ErikE, Jul 10 '09 at 18:50
@Emtucifo -silly indeed...why listen to all those sql guys that recommend using calendar tables to simplify date calculations...silly indeed. — , Jul 10 '09 at 19:17
table of numbers is what I use all the time...but I do have a Holiday table for 84 countries nearby — SQLMenace, Jul 10 '09 at 19:21
I'll eat my words if you can show me a use for calendar tables that 1) isn't for complex holiday/workday rules, 2) can't be easily done with built-in functions, and 3) only if not #2, then a Numbers table works just as well and doesn't require populating the table with specific dates. — ErikE, Jul 10 '09 at 19:27
Data warehouses make extensive use of calendar tables. The precalculated months, quarters, years, etc provide the end users with fields they can filter/aggregate against. — David Rushton, Dec 24 '15 at 10:08

score 786 · Accepted Answer · edited Sep 20 '17 at 04:37

786

Use DATENAME or DATEPART:

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

edited Sep 20 '17 at 04:37

Kols

3,641
2
34
42

answered Jul 10 '09 at 17:51

SQLMenace

132,095
25
206
225

thx, I had tried datename but used d, gave me an integer back. dw works as expected – Jul 10 '09 at 17:59
50

That's why I prefer to use select datename(weekday,getdate()). I don't have to remember that dw returns weekday.... when I can use "weekday" instead. – George Mastros Jul 10 '09 at 18:06
12

anyone glancing over this - note that the **default start of week is sunday**. Look at @Sung's answer for how to change that safely – JonnyRaa Mar 31 '14 at 09:55
2

@niico it is because SQL indexes from 1 while C like languages index from 0. – user824276 Mar 17 '18 at 16:22
finding this answer today, and it creeps me out that the sample result on your answer matches mine, then found out that days on july 2009 is the same as days today (july 2020), anyways thanks for the easy answer. – Henry Jul 17 '20 at 02:40

score 105 · Answer 2 · edited Jun 01 '21 at 18:27

105

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

edited Jun 01 '21 at 18:27

Marcello Miorelli

3,368
4
44
67

answered Jul 11 '09 at 14:59

dance2die

35,807
39
131
194

38

So to get constant datepart value, you do `( @@datefirst - 1 + datepart(weekday, thedate) ) % 7`. Sunday will always be zero. – Endy Tjahjono Feb 23 '12 at 03:52
1

kindof horrible this stuff is static so you have to follow the query original, change value, run query, reset original pattern – JonnyRaa Mar 31 '14 at 09:53
3

Funny part of this is that .NET's `DayOfWeek` enumeration has `DayOfWeek.Sunday`with a value of ... `0`. So, no matter what `DateFirst` is setted to, an _untreated_ SQL-returned `WEEKDAY` value will never be compatible to the .NET counterpart. Yay, Microsoft. – Eric Wu Sep 04 '16 at 23:41

score 31 · Answer 3 · edited Oct 08 '15 at 18:09

31

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

edited Oct 08 '15 at 18:09

Gary Kindel

17,071
7
49
66

answered Mar 29 '12 at 20:36

Sutirth

922
10
14

31

You should know that ther is a built in function to achieve the same. `select datename(dw,getdate())` – Soham Dasgupta Apr 30 '12 at 12:20
12

@SohamDasgupta this built in function does not return the same result for non-english version of MS SQL. – Mr Scapegrace Nov 23 '16 at 11:14

score 17 · Answer 4 · answered Sep 02 '14 at 11:32

17

EUROPE:

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

answered Sep 02 '14 at 11:32

npg

171
1
2

3

Please include explanation of what your code does and how it answers the question. If you get a code snippet as an answer, you may not know what to do with it. Answer should give the OP and future visitors guidance on how to debug and fix their problem. Pointing out, what the idea behind your code is, greatly helps in understanding the issue and applying or modifying your solution. – Palec Sep 02 '14 at 12:01
This answer should be improved, but it's still the only one that gives me the values I want without a call to `SET DATEFIRST`. – aboy021 Jun 11 '21 at 04:08

score 11 · Answer 5 · edited Apr 26 '19 at 21:30

11

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.

edited Apr 26 '19 at 21:30

animuson

53,861
28
137
147

answered Oct 07 '11 at 16:02

lazerwire.com

135
1
2

8

Please put the answer into the answer so people don't have to click a link to get to it. – Martin Smith Oct 07 '11 at 16:10
This didn't help me at all, It is just a clickbait – Alvaro Parra Jan 24 '21 at 13:20

score 10 · Answer 6 · answered Oct 18 '16 at 20:12

10

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

SELECT FORMAT(GETDATE(), 'dddd')

answered Oct 18 '16 at 20:12

Chris Stillwell

10,266
10
67
77

score 3 · Answer 7 · edited Jun 20 '19 at 08:51

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

score 3 · Answer 8 · answered Feb 07 '19 at 15:37

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

score 2 · Answer 9 · answered Sep 20 '17 at 15:45

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 return the weekday number (1 to 7) ever since January 1st respectively 7th, 1753.

Sathish · Answer 10 · 2021-10-10T09:58:02.250

1

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

SELECT DATENAME(WEEKDAY,GETDATE()) AS WeekDay

or

SELECT DATENAME(W,GETDATE()) AS WeekDay

edited Oct 10 '21 at 09:58

answered Oct 10 '21 at 09:51

Sathish

49
3

score 0 · Answer 11 · edited Oct 16 '21 at 07:05

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

This is obtuse code, and gives no indication of what it is doing or trying to achieve — brewmanz, May 28 '17 at 20:16

Get day of week in SQL Server 2005/2008

11 Answers11

Linked

Related