在Where子句中使用datepart的情况

I use a view to generate affected rows. A table t1 has five columns with Mo, Tu, We, Th and Fr. Each columns has 0 if this day is not relevant or 1 if this day is relevant (e.g. 1,0,1,0,1 - Mo, We and Fr are relevant, Tu, Th are not relevant). Now I define the day actual date + 1 and will receive all rows which have a 1 in the affected column.

我使用视图生成受影响的行。表t1有5列，分别为Mo、Tu、We、Th和Fr。如果这一天不相关，则为0;如果这一天相关，则为1。现在我定义了实际日期+ 1，并将接收受影响列中有1的所有行。

...
WHERE
CASE SUBSTRING(DATEPART(WEEKDAY, GETDATE() + 1), 1, 2)
WHEN 'Mo' THEN t1.Mo = 1
WHEN 'Tu' THEN t1.Tu = 1
...

but this doesn't work. How can i solve this problem?

但这是行不通的。我如何解决这个问题?

4 个解决方案

#1

DATEPART return a number, in case of WEEKDAY ranging (by default) from 1 (Sunday) to 7 (Saturday)

DATEPART返回一个数字，如果工作日(默认情况下)从1(星期日)到7(星期六)

WHERE
-- choose the relevant column
CASE DATEPART(WEEKDAY, GETDATE() + 1)
  WHEN 2 THEN t1.Mo
  WHEN 3 THEN t1.Tu
  WHEN 4 THEN t1.We
  WHEN 5 THEN t1.Th
  WHEN 6 THEN t1.Fr
-- compare with the expected value
END = 1

#2

You need to separate the value the case returns with the comparison, which is a different thing:

您需要将案例返回的值与比较分开，这是另一回事:

WHERE
1 = CASE SUBSTRING(DATEPART(WEEKDAY, GETDATE() + 1), 1, 2)
         WHEN 'Mo' THEN t1.Mo
         WHEN 'Tu' THEN t1.Tu
         -- ...
    END

#3

DATEPART with WEEKDAY comes back with an INT value stating the day's index within a week.

工作日的DATEPART返回一个INT值，表示一周内的日指数。

Be carefull: This is depending on the system's culture! Have a look on @@DATEFIRST

注意:这取决于系统的文化!查看@@DATEFIRST

Don't know what exactly you want to achieve but your code cannot work. There's no SUBSTRING with INT...

不知道您想要实现什么，但是您的代码不能工作。没有带INT的子字符串…

maybe something like this? This query will only return something on a day with index 1 or 2...

也许是这样的?这个查询只会在一天内返回索引1或2…

WHERE
1=CASE DATEPART(WEEKDAY, GETDATE() + 1)
WHEN 1 THEN 1
WHEN 2 THEN 1
WHEN 3 THEN 2
END

#4

According to MSDN, using WEEKDAY with DATEPART is going to return an integer, not a string. You'll need to check for those integer values instead of calling substring and checking for things like 'Mo', 'Tu'

根据MSDN，使用WEEKDAY with DATEPART将返回一个整数，而不是字符串。你需要检查这些整数值而不是调用子字符串检查像Mo, Tu

Here's a brief example. Also note that depending on settings (again, take a look at the MSDN article) the first day of the week could be different. Below I'm assuming that day 1 is Sunday.

这是一个简单的例子。还请注意，根据设置(再次查看MSDN文章)，一周的第一天可能会有所不同。下面我假设第一天是星期天。

SELECT CASE DATEPART(WEEKDAY, GETDATE() + 1) 
    WHEN 1 THEN 'Sunday' 
    WHEN 2 THEN 'Monday' 
    ELSE 'Other days' END

#1