在 SQL Server 2005/2008 中获取星期几

Get day of week in SQL Server 2005/2008

提问by SQLMenace

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

如果我有一个日期 01/01/2009，我想知道它是哪一天，例如星期一、星期二等...

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

SQL Server 2005/2008 中是否有内置函数？还是我需要使用辅助表？

回答by SQLMenace

Use DATENAMEor DATEPART:

使用DATENAME或DATEPART：

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

回答by dance2die

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

尽管 SQLMenace 的答案已被接受，但SET您应该注意一个重要的选项

SET DATEFIRST

先设置日期

DATENAMEwill return correct date namebut not the same DATEPARTvalue if the first day of week has been changed as illustrated below.

如果如下图所示更改了一周的第一天，则DATENAME将返回正确的日期名称，但不会返回相同的DATEPART值。

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

回答by Sutirth

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

回答by lazerwire.com

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.

要获得给定日期的星期几的确定性值，您可以使用DATEPART ()和@@datefirst的组合。否则取决于服务器上的设置。

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

查看以下站点以获得更好的解决方案：MS SQL：星期几

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.

星期几将在 0 到 6 的范围内，其中 0 是星期日，1 是星期一等。然后您可以使用简单的 case 语句返回正确的工作日名称。

回答by npg

EUROPE:

欧洲：

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

回答by Chris Stillwell

With SQL Server 2012 and onward you can use the FORMATfunction

使用 SQL Server 2012 及更高版本，您可以使用该FORMAT函数

SELECT FORMAT(GETDATE(), 'dddd')

回答by Tapan kumar

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

这是我的代码的工作副本 检查它，如何从 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

回答by Michel de Ruiter

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

如果您不想依赖@@DATEFIRST或使用DATEPART(weekday, DateColumn)，只需自己计算星期几。

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.

这将返回自 1753 年 1 月 1 日和 7 日以来的工作日数（1 到 7）。

回答by Minh Nguyen

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

您可以使用DATEPART(dw, GETDATE())但请注意，结果将取决于 SQL 服务器设置@@DATEFIRST值，即一周中的第一天设置（在欧洲，默认值 7 为星期日）。

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

如果您想将一周的第一天更改为另一个值，您可以使用，SET DATEFIRST但这可能会影响您不想要的查询会话中的任何地方。

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

另一种方法是明确指定一周中的第一天值作为参数并避免依赖于@@DATEFIRST设置。您可以在需要时使用以下公式来实现：

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

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

@WeekStartDay您希望系统每周的第一天在哪里（从 1 到 7，这意味着从周一到周日）。

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)

用法示例： GetDayInWeek('2019-02-04 00:00:00', 1)

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

它等效于以下（但独立于 SQL 服务器 DATEFIRST 设置）：

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

回答by Reto

You may find this version usefull.

您可能会发现此版本很有用。

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