在SQL Server中从给定的日期获取工作日名称。

I am trying get a day name like friday, saturday, sunday, monday etc from a given date. I know there is a built in function which returns the day name for example:

我正在尝试从一个给定的日期中获得一个像星期五、星期六、星期天、星期一等的日名。我知道有一个内置函数，它返回日名，例如:

SELECT DATENAME(dw,'09/23/2013') as theDayName

this SQL query returns:

这个SQL查询返回:

'Monday'

“周一”

This is all OK. But I would like to pass Month, Day and Year individually.

这都是好的。但是我想一个月，一天，一年地过下去。

I am using the builtin DATEPART function to retrieve month, day and year from a date so I can pass it to the DATENAME function:

我正在使用builtin DATEPART函数从日期中检索月、日和年，以便将其传递到DATENAME函数:

SELECT DATEPART(m, GETDATE()) as theMonth  -- returns 11
SELECT DATEPART(d, GETDATE()) as theDay   -- returns 20
SELECT DATEPART(yy, GETDATE()) as theYear   -- returns 2013

Now that I have Month, Day, Year values individually, I pass it to my DATENAME to get the Weekname of the date I want:

现在我有了月、日、年的单独值，我将它传递到我的DATENAME以获得我想要的日期的Weekname:

--my SQL query to return dayName
SELECT (DATENAME(dw, DATEPART(m, GETDATE())/DATEPART(d, myDateCol1)/ DATEPART(yy, getdate())))  as myNameOfDay, FirstName, LastName FROM myTable

This returns an incorrect Day Name. I tried replace / with - so that in the DATENAME function my SQL query becomes:

这将返回一个不正确的日名。我尝试使用replace / with -，以便在DATENAME函数中，我的SQL查询变为:

SELECT DATENAME(dw,'09/23/2013') 
--becomes
SELECT DATENAME(dw,'09-23-2013')

but it still returns incorrect dayName from my SQL query. Am I missing something here.

但它仍然从我的SQL查询返回错误的dayName。我是不是漏掉了什么。

Please advise.

请建议。

6 个解决方案

#1

You need to construct a date string. You're using / or - operators which do MATH/numeric operations on the numeric return values of DATEPART. Then DATENAME is taking that numeric value and interpreting it as a date.

您需要构造一个日期字符串。您正在使用/ or -操作符，它们对DATEPART的数值返回值进行数学/数值操作。然后DATENAME将这个数值转换为日期。

You need to convert it to a string. For example:

你需要把它转换成一个字符串。例如:

SELECT (
  DATENAME(dw, 
  CAST(DATEPART(m, GETDATE()) AS VARCHAR) 
  + '/' 
  + CAST(DATEPART(d, myDateCol1) AS VARCHAR) 
  + '/' 
  + CAST(DATEPART(yy, getdate()) AS VARCHAR))
  )

#2

Tested and works on SQL 2005 and 2008. Not sure f0r 2012. The solution uses DATENAME instead of DATEPART

测试并运行SQL 2005和2008。不确定f0r 2012。解决方案使用DATENAME而不是DATEPART。

select datename(dw,getdate()) --Thursday
select datepart(dw,getdate()) --2

#3

If you have SQL Server 2012:

如果您有SQL Server 2012:

If your date parts are integers then you can use DATEFROMPARTS function.

如果您的日期部分是整数，那么您可以使用DATEFROMPARTS函数。

SELECT DATENAME( dw, DATEFROMPARTS( @Year, @Month, @Day ) )

If your date parts are strings, then you can use the CONCAT function.

如果您的日期部分是字符串，那么您可以使用CONCAT函数。

SELECT DATENAME( dw, CONVERT( date, CONCAT( @Day, '/' , @Month, '/', @Year ), 103 ) )

#4

Try like this: select DATENAME(DW,GETDATE())

尝试这样:选择DATENAME(DW,GETDATE())

#5

SELECT DATENAME(DW,CONVERT(VARCHAR(20),GETDATE(),101))

#6

-1

I used

我使用

select
case
when (extract (weekday from DATE)=0) then 'Sunday'

and so on...

等等……

0 Sunday, 1 Monday...

周日0,1周一…

#1