使用 T-SQL 获取上周五的日期，除非今天是周五 [英] Get last Friday's Date unless today is Friday using T-SQL

问题描述

我正在尝试获取正确的 SQL 代码以获取上周五的日期.几天前，我以为我的代码是正确的.但只是注意到它正在获取上周的星期五日期，而不是最后一个星期五.我写这个问题的那天是星期六，2012 年 8 月 11 日上午 12:23.在 SQL Server 中，此代码返回 2012 年 8 月 3 日星期五.但是，我希望它在 2012 年 8 月 10 日星期五返回.我该如何修复此代码?由于我们在这里讨论细节，如果当前日期是星期五，那么我想返回今天的日期.因此，如果是昨天 (8/10/2012) 并且我昨天运行了此代码，那么我希望此代码返回 8/10/2012，而不是 8/3/2012.

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.

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

推荐答案

试试这个:

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

结果:

2012-08-03 

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

结果:

  2012-08-10 

这篇关于使用 T-SQL 获取上周五的日期，除非今天是周五的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！