在T-SQL中确定“本周”。

This is locale specific to the US wherein it considered that the start of a week is Sunday; I want to be able to ask SQL to give me the date of the next Sunday relative to today [getDate()]. If today is Jan 15 it should return Jan 18; if today were Sunday it should return the following Sunday which is the 25th. This would be trivial to write a UDF for but I was curious if anyone had other tricks/ideas?

这是美国特有的场所，它认为一周的开始是星期天;我希望能够要求SQL给我下个星期天相对于今天的日期[getDate()]。如果今天是1月15日，它应该在1月18日返回;如果今天是星期天，它应该在下个星期天也就是25号。写一个UDF是很简单的，但是我很好奇是否有人有其他的技巧和想法?

2 个解决方案

#1

DECLARE @d AS datetime
SET @d = '1/15/2009'
PRINT @d
PRINT DATEADD(day, 8 - DATEPART(weekday, @d), @d)
SET @d = '1/18/2009'
PRINT @d
PRINT DATEADD(day, 8 - DATEPART(weekday, @d), @d)

-- So it should be able to be used inline pretty efficiently:
DATEADD(day, 8 - DATEPART(weekday, datecolumn), datecolumn)

-- If you want to change the first day for a different convention, simply use SET DATEFIRST before performing the operation
-- e.g. for Monday: SET DATEFIRST 1
-- e.g. for Saturday: SET DATEFIRST 6

DECLARE @restore AS int
SET @restore = @@DATEFIRST
SET DATEFIRST 1

DECLARE @d AS datetime
SET @d = '1/15/2009'
PRINT @d
PRINT DATEADD(day, 8 - DATEPART(weekday, @d), @d)
SET @d = '1/19/2009'
PRINT @d

PRINT DATEADD(day, 8 - DATEPART(weekday, @d), @d)
SET DATEFIRST @restore

#2

Today's day-of-week:
SELECT @dow = DATEPART(d, GETDATE()) where 1 = Sunday, 7 = Saturday

今天的星期:选择@dow = DATEPART(d, GETDATE())，其中1 = Sunday, 7 = Saturday

You want to add enough days to get the next Sunday.

你想增加足够的天数来度过下一个星期天。

If today is 1 = Sunday, add 7
If today is 2 = Monday, add 6
If today is 3 = Tuesday, add 5 etc.

如果今天是1 =星期天，再加7如果今天是2 =星期一，再加6如果今天是3 =星期二，再加5等等。

so you are always adding 8 - today's day-of-week value.

所以你总是增加8 -今天的周值。

SELECT DATEADD(d, GETDATE(), 8 - @dow(GETDATE))

选择DATEADD(d, GETDATE()， 8 - @dow(GETDATE))

EDIT: But Cade wins!

编辑:但是凯德赢了!