Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Get day of week in SQL Server 2005/2008

People also ask

How do I get the day of the week in SQL Server?

MySQL DAYOFWEEK() Function The DAYOFWEEK() function returns the weekday index for a given date (a number from 1 to 7). Note: 1=Sunday, 2=Monday, 3=Tuesday, 4=Wednesday, 5=Thursday, 6=Friday, 7=Saturday.

How do I get the day of the week name in SQL?

Method 1: DateName() Function for Day Name DECLARE @DateVal DATE = '2020-07-27' ; SELECT @DateVal As [ Date ], DATENAME(WEEKDAY, @DateVal) AS [ Day Name ];

How extract day from date in SQL Server?

If you want to get a day from a date in a table, use the SQL Server DAY() function. This function takes only one argument – the date. This can be a date or date and time data type. (In our example, the column VisitDate is of the date data type.)

How do I get the first day of a week from a date in SQL?

Option 1: Sunday as the First Day of the WeekDATEADD(week, DATEDIFF(week, -1, RegistrationDate), -1) AS Sunday; The function DATEADD() takes three arguments: a datepart, a number, and a date.


Use DATENAME or DATEPART:

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

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

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

EUROPE:

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

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.


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

SELECT FORMAT(GETDATE(), 'dddd')