Geek Answers Handbook

Define "this week" in T-SQL

This is a US-specific language where he believes Sunday is the beginning of the week; I want to be able to ask SQL to give me the date next Sunday relative to today [getDate ()]. If today is January 15th, he must return on January 18th; if it was Sunday today, he should return next Sunday, which is the 25th. It would be trivial to write UDF, but I was curious if anyone has any other tricks / ideas?

+3
source share
2 answers
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
+5
source

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

, .

1 = , 7
2 = , 6
3 = , 5 .

8 - .

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

EDIT: !

+2

More articles:


All Articles