如何获取星期六的日期（或任何其他工作日的日期）- SQL Server
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6982061/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to get Saturday's Date (Or any other weekday's Date)- SQL Server
提问by Sreekumar P
How to get Saturday's Date. I have today's date with me.
如何获得星期六的日期。我有今天的约会。
GETDATE()
How to do this.
这该怎么做。
For eg. TODAY is 08-08-2011
例如。今天是08-08-2011
I want output as 08-13-2011
我想输出为 08-13-2011
采纳答案by leoinfo
This is a function that will return the next Saturday if you call it like this:
这是一个函数，如果你这样调用它，它将在下周六返回：
SELECT dbo.fn_Get_NextWeekDay('2011-08-08', 6)
The "6" comes from the list of possible valuesyou can set for DATEFIRST.
“6”来自您可以为 设置的可能值列表DATEFIRST。
You can get any other day of the week by changing the second parameter accordingly.
您可以通过相应地更改第二个参数来获得一周中的任何其他日期。
This is the function:
这是函数：
IF OBJECT_ID('dbo.fn_Get_NextWeekDay') IS NOT NULL
DROP FUNCTION dbo.fn_Get_NextWeekDay
GO
CREATE FUNCTION dbo.fn_Get_NextWeekDay(
@aDate DATETIME
, @dayofweek INT
/*
@dw - day of the week
1 - Monday
2 - Tuesday
3 - Wednesday
4 - Thursday
5 - Friday
6 - Saturday
7 - Sunday
*/
)
RETURNS DATETIME
AS
/*
SELECT dbo.fn_Get_NextWeekDay('2011-08-08', 6)
SELECT dbo.fn_Get_NextWeekDay('2011-08-08', 1)
*/
BEGIN
RETURN
DATEADD(day
, ( @dayofweek + 8 - DATEPART(dw, @aDate) - @@DATEFIRST ) % 7
, @aDate
)
END
GO
[EDIT]This might be another solution. This should work in any language:
[编辑]这可能是另一种解决方案。这应该适用于任何语言：
IF OBJECT_ID('dbo.fn_NextWeekDay') IS NOT NULL
DROP FUNCTION dbo.fn_NextWeekDay
GO
CREATE FUNCTION dbo.fn_NextWeekDay(
@aDate DATE
, @dayofweek NVARCHAR(30)
)
RETURNS DATE
AS
/*
SELECT dbo.fn_NextWeekDay('2016-12-14', 'fri')
SELECT dbo.fn_NextWeekDay('2016-03-15', 'mon')
*/
BEGIN
DECLARE @dx INT = 6
WHILE UPPER(DATENAME(weekday,@aDate)) NOT LIKE UPPER(@dayofweek) + '%'
BEGIN
SET @aDate = DATEADD(day,1,@aDate)
SET @dx=@dx-1
if @dx < 0
BEGIN
SET @aDate = NULL
BREAK
END
END
RETURN @aDate
END
GO
回答by nvogel
Use a Calendar table (table with one row per date):
使用日历表（每个日期一行的表）：
SELECT MIN(DateValue) DateValue
FROM Calendar
WHERE DateValue >= CURRENT_TIMESTAMP
AND DayOfWeek = 'Saturday';
回答by Daniel Hilgarth
回答by Andy Hunt
DECLARE @Today date = 'TODAYS-DATE';
DECLARE @TodayNumber int = DATEPART(dw, @Today) -- Get the day number
DECLARE @Saturday date = DATEADD(DAY, (6-@TodayNumber)%7, @Today)
-- Add the number of days between today and saturday (the 6th day), modulus 7 to stop you adding negative days
Hope that helps!
希望有帮助！
回答by mrtig
Another approach to this takes two steps, but might be more readable (look ma, no modulus):
另一种方法需要两个步骤，但可能更具可读性（看，没有模数）：
- Go back to last saturday:
DATEADD(DAY, -1 * datepart(weekday, GETDATE()), getdate()) - Then, add on a week:
DATEADD(WEEK, 1, @lastSaturday, getdate()))
- 回到上周六：
DATEADD(DAY, -1 * datepart(weekday, GETDATE()), getdate()) - 然后，添加一周：
DATEADD(WEEK, 1, @lastSaturday, getdate()))
The whole thing:
整个东西：
declare @today DATETIME = GETDATE()
declare @lastSaturday DATETIME = DATEADD(DAY, -1 * datepart(weekday, @today), @today)
declare @nextSaturday DATETIME = DATEADD(WEEK, 1, @lastSaturday)
Or, if you're ok with @todaybeing GETDATE(), you can do the calculation all at once:
或者，如果你有好@today幸福GETDATE()，你可以一下子做计算：
SELECT DATEADD(WEEK, 1, DATEADD(DAY, -1 * datepart(weekday, GETDATE()), getdate()))
回答by leoinfo
Try this :
尝试这个 ：
SET DATEFIRST 7
DECLARE @d DATETIME
SET @d = '2011-08-08' --GETDATE()
SELECT NEXT_SAT = DATEADD(day, (7 + @@DATEFIRST - DATEPART(dw, @d)) % 7, @d )
回答by jdavies
Checkout the SQL DATEADD function.
签出 SQL DATEADD 函数。
Which you can use this along with DATEPART function to return the correct date.
您可以将它与 DATEPART 函数一起使用来返回正确的日期。
