获取上周五的日期，除非今天是星期五使用T-SQL

I'm trying to get the correct SQL code to obtain last Friday's date. A few days ago, I thought I had my code correct. But just noticed that it's getting last week's Friday date, not the last Friday. The day I'm writing this question is Saturday, 8/11/2012 @ 12:23am. In SQL Server, this code is returning Friday, 8/3/2012. However, I want this to return Friday, 8/10/2012 instead. How can I fix this code? Since we're getting to specifics here, if the current day is Friday, then I want to return today's date. So if it were yesterday (8/10/2012) and I ran this code yesterday, then I would want this code to return 8/10/2012, not 8/3/2012.

我正在尝试获取正确的SQL代码以获取上周五的日期。几天前，我以为我的代码是正确的。但只是注意到它已经到了上周的星期五，而不是上周五。我写这个问题的那天是星期六，8/11/2012 @ 12:23 am。在SQL Server中，此代码将于2012年8月3日星期五返回。但是，我希望这会在周五，8/10/2012返回。我该如何修复此代码？由于我们在这里详细说明，如果当天是星期五，那么我想回到今天的日期。所以，如果它是昨天（2012年8月8日）并且我昨天运行了这段代码，那么我希望这段代码返回8/10/2012，而不是8/3/2012。

SELECT DATEADD(DAY, -3, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0))

11 个解决方案

#1

try this:

尝试这个：

declare @date datetime;
set @date='2012-08-09'
SELECT case when datepart(weekday, @date) >5 then
 DATEADD(DAY, +4, DATEADD(WEEK, DATEDIFF(WEEK, 0, @date), 0)) 
else DATEADD(DAY, -3, DATEADD(WEEK, DATEDIFF(WEEK, 0, @date), 0)) end

result:

结果：

2012-08-03

Example2:

例2：

declare @date datetime;
set @date='2012-08-10'
SELECT case when datepart(weekday, @date) >5 then
 DATEADD(DAY, +4, DATEADD(WEEK, DATEDIFF(WEEK, 0, @date), 0)) 
else DATEADD(DAY, -3, DATEADD(WEEK, DATEDIFF(WEEK, 0, @date), 0)) end

result:

结果：

  2012-08-10

11 个解决方案

#1

更多相关文章

随机推荐