blocks|key|1292622|text|使用DATENAME或DATEPART|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1292623|SELECT+DATENAME(dw,GETDATE())+--+Friday
SELECT+DATEPART(dw,GETDATE())+--+6|code-block|syntax|javascript|1292624|entityMap|0|LINK|mutability|MUTABLE|url|https://docs.microsoft.com/en-us/sql/t-sql/functions/datename-transact-sql|1|https://docs.microsoft.com/en-us/sql/t-sql/functions/datepart-transact-sql^0|2|8|B|8|2|8|0|B|8|1|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@$9|V|A|W|B|C]|$9|X|A|Y|B|C]]|D|@$9|Z|A|10|1|11]|$9|12|A|13|1|14]]|E|$]]|$1|F|3|G|5|H|7|15|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|16|8|@]|D|@]|E|$]]]|L|$M|$5|N|O|P|E|$Q|R]]|S|$5|N|O|P|E|$Q|T]]]]

Use <a href="https://docs.microsoft.com/en-us/sql/t-sql/functions/datename-transact-sql" rel="noreferrer"><code>DATENAME</code></a> or <a href="https://docs.microsoft.com/en-us/sql/t-sql/functions/datepart-transact-sql" rel="noreferrer"><code>DATEPART</code></a>:

<pre><code>SELECT DATENAME(dw,GETDATE()) -- Friday
SELECT DATEPART(dw,GETDATE()) -- 6
</code></pre>

blocks|key|1507254|text|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|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1507255|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>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
</code></pre>

blocks|key|1507482|text|欧洲：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1507483|declare+@d+datetime;
set+@d=getdate();
set+@dow=((datepart(dw,@d)+%2B+@@DATEFIRST-2)+%25+7%2B1);|code-block|syntax|javascript|1507484|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

EUROPE:

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

blocks|key|1292674|text|要获得给定日期的星期几的确定性值，可以组合使用DATEPART()和@@datefirst。否则，您将依赖于服务器上的设置。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1292675|请查看以下站点以获得更好的解决方案：MS+SQL:+Day+of+Week|1292676|星期的日期将在0到6的范围内，其中0是星期天，1是星期一，依此类推。然后，您可以使用简单的case语句来返回正确的工作日名称。|1292677|entityMap|0|LINK|mutability|MUTABLE|url|https://docs.microsoft.com/en-in/sql/t-sql/functions/datepart-transact-sql|1|http://msdn.microsoft.com/en-us/library/ms187766%2528v=sql.105%2529.aspx|2|http://web.archive.org/web/20180112090549/http://www.lazerwire.com/2011/10/sql-day-of-week.html^0|N|A|0|Y|B|1|0|I|J|2|0|0^^$0|@$1|2|3|4|5|6|7|T|8|@]|9|@$A|U|B|V|1|W]|$A|X|B|Y|1|Z]]|C|$]]|$1|D|3|E|5|6|7|10|8|@]|9|@$A|11|B|12|1|13]]|C|$]]|$1|F|3|G|5|6|7|14|8|@]|9|@]|C|$]]|$1|H|3|-4|5|6|7|15|8|@]|9|@]|C|$]]]|I|$J|$5|K|L|M|C|$N|O]]|P|$5|K|L|M|C|$N|Q]]|R|$5|K|L|M|C|$N|S]]]]

To get a deterministic value for the day of week for a given date you could use a combination of <a href="https://docs.microsoft.com/en-in/sql/t-sql/functions/datepart-transact-sql" rel="nofollow noreferrer">DATEPART()</a> and <a href="http://msdn.microsoft.com/en-us/library/ms187766%28v=sql.105%29.aspx" rel="nofollow noreferrer">@@datefirst</a>. Otherwise your dependent on the settings on the server.

Check out the following site for a better solution:
<a href="http://web.archive.org/web/20180112090549/http://www.lazerwire.com/2011/10/sql-day-of-week.html" rel="nofollow noreferrer">MS SQL: Day of Week</a>

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.

blocks|key|1507543|text|在SQL+Server2012及更高版本中，您可以使用FORMAT函数|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1507544|SELECT+FORMAT(GETDATE(),+'dddd')|code-block|syntax|javascript|1507545|entityMap^0|R|6|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

With SQL Server 2012 and onward you can use the <code>FORMAT</code> function

<pre><code>SELECT FORMAT(GETDATE(), 'dddd')
</code></pre>

blocks|key|1507277|text|这是我的代码的工作副本检查它，如何在sql中从日期检索日期名称|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1507278|CREATE+Procedure+[dbo].[proc_GetProjectDeploymentTimeSheetData]+
@FromDate+date,
@ToDate+date

As+
Begin
select+p.ProjectName+%2B+'+(+'+%2B+st.Time+%2B'+'%2B'-'%2B'+'%2Bet.Time+%2B'+)'+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|code-block|syntax|javascript|1507279|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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

<pre><code>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 &gt;= @FromDate
and pts.StartDate &lt;= @ToDate
END
</code></pre>

blocks|key|1507590|text|如果您不想依赖@@DATEFIRST或使用DATEPART(weekday,+DateColumn)，只需自己计算星期几即可。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1507591|对于以周一为基础的周(欧洲)，最简单的是：|1507592|SELECT+DATEDIFF(day,+'17530101',+DateColumn)+%25+7+%2B+1+AS+MondayBasedDay|code-block|syntax|javascript|1507593|对于基于周日的周(美国)使用：|1507594|SELECT+DATEDIFF(day,+'17530107',+DateColumn)+%25+7+%2B+1+AS+SundayBasedDay|1507595|此返回值分别为1753年1月1日以来的工作日数(1到7)。|1507596|entityMap^0|7|B|L|T|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@$9|V|A|W|B|C]|$9|X|A|Y|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|Z|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|10|8|@]|D|@]|E|$K|L]]|$1|M|3|N|5|6|7|11|8|@]|D|@]|E|$]]|$1|O|3|P|5|J|7|12|8|@]|D|@]|E|$K|L]]|$1|Q|3|R|5|6|7|13|8|@]|D|@]|E|$]]|$1|S|3|-4|5|6|7|14|8|@]|D|@]|E|$]]]|T|$]]

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

For Monday based weeks (Europe) simplest is:

<pre><code>SELECT DATEDIFF(day, '17530101', DateColumn) % 7 + 1 AS MondayBasedDay
</code></pre>

For Sunday based weeks (America) use:

<pre><code>SELECT DATEDIFF(day, '17530107', DateColumn) % 7 + 1 AS SundayBasedDay
</code></pre>

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

blocks|key|1293128|text|您可以使用DATEPART(dw,+GETDATE())，但请注意，结果将取决于SQL+server设置的周日值，这是一周的第一天设置(在欧洲，默认值为7，即@@DATEFIRST+)。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1293129|如果您想将一周的第一天更改为另一个值，您可以使用SET+DATEFIRST，但这可能会影响查询会话中您不想要的所有地方。|1293130|另一种方法是显式地将每周的第一天值指定为参数，并避免依赖于@@DATEFIRST设置。您可以在需要时使用以下公式来实现这一点：|1293131|(DATEPART(dw,+GETDATE())+%2B+@@DATEFIRST+%2B+6+-+@WeekStartDay)+%25+7+%2B+1|code-block|syntax|javascript|1293132|其中，@WeekStartDay是您希望系统在一周中的第一天(从1到7，意味着从周一到周日)。|1293133|我已经将它封装到下面的函数中，这样我们就可以很容易地重用它：|1293134|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)+%2B+@@DATEFIRST+%2B+6+-+@WeekStartDay)+%25+7+%2B+1+
END|1293135|示例用法：GetDayInWeek('2019-02-04+00:00:00',+1)|1293136|它等效于以下内容(但独立于SQL+server+DATEFIRST设置)：|1293137|SET+DATEFIRST+1
DATEPART(dw,+'2019-02-04+00:00:00')|1293138|entityMap^0|5|N|27|B|0|O|D|0|T|B|0|0|3|D|0|0|0|5|12|0|0|0^^$0|@$1|2|3|4|5|6|7|12|8|@$9|13|A|14|B|C]|$9|15|A|16|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|17|8|@$9|18|A|19|B|C]]|D|@]|E|$]]|$1|H|3|I|5|6|7|1A|8|@$9|1B|A|1C|B|C]]|D|@]|E|$]]|$1|J|3|K|5|L|7|1D|8|@]|D|@]|E|$M|N]]|$1|O|3|P|5|6|7|1E|8|@$9|1F|A|1G|B|C]]|D|@]|E|$]]|$1|Q|3|R|5|6|7|1H|8|@]|D|@]|E|$]]|$1|S|3|T|5|L|7|1I|8|@]|D|@]|E|$M|N]]|$1|U|3|V|5|6|7|1J|8|@$9|1K|A|1L|B|C]]|D|@]|E|$]]|$1|W|3|X|5|6|7|1M|8|@]|D|@]|E|$]]|$1|Y|3|Z|5|L|7|1N|8|@]|D|@]|E|$M|N]]|$1|10|3|-4|5|6|7|1O|8|@]|D|@]|E|$]]]|11|$]]

You can use <code>DATEPART(dw, GETDATE())</code> but be aware that the result will rely on SQL server setting <code>@@DATEFIRST</code> 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 <code>SET DATEFIRST</code> 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 <code>@@DATEFIRST</code> setting. You can use the following formula to achieve that when need it:

<pre><code>(DATEPART(dw, GETDATE()) + @@DATEFIRST + 6 - @WeekStartDay) % 7 + 1
</code></pre>

where <code>@WeekStartDay</code> 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:

<pre><code>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
</code></pre>

Example usage: 
<code>GetDayInWeek('2019-02-04 00:00:00', 1)</code>

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

<pre><code>SET DATEFIRST 1
DATEPART(dw, '2019-02-04 00:00:00')
</code></pre>

blocks|key|1507309|text|您可能会发现此版本对于在一行中返回缩短的工作日名称很有用。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1507310|--+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)%2B@@datefirst-1)*4%2B1,3),Datum
++++++++from+#test+|code-block|syntax|javascript|1507311|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

You may find this version useful for returning a shortened weekday name in one line.
<pre><code>-- 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 
</code></pre>

blocks|key|1507744|text|除了以上所有答案，dw代表Week+Day|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1507745|SELECT+DATENAME(WEEKDAY,GETDATE())+AS+WeekDay|code-block|syntax|javascript|1507746|或|1507747|SELECT+DATENAME(W,GETDATE())+AS+WeekDay|1507748|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

In addition all with above answers, dw stands for Week Day
<pre><code>SELECT DATENAME(WEEKDAY,GETDATE()) AS WeekDay
</code></pre>
or
<pre><code>SELECT DATENAME(W,GETDATE()) AS WeekDay
</code></pre>

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?

Get day of week in SQL Server 2005/2008

SQL Server

服务器

如果我有2009年1月1日的日期，我想知道是哪一天，例如星期一、星期二等。在SQL Server 2005/2008中有内置的函数吗？或者我需要使用辅助表？

问获取SQL Server 2005/2008中的星期几
EN

回答 10

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问获取SQL Server 2005/2008中的星期几EN

回答 10

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问获取SQL Server 2005/2008中的星期几
EN