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?
+4
A:
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
Cade Roux
2009-01-16 04:10:33
using your in my code now, works just fine although in SQL 2008 I do a cast to just 'date' so that the time isnt returned as part of it. I will let this float for a day or so and see if anything better shows up, OTW it is Cade FTW!
keithwarren7
2009-01-16 04:42:07
What's the generalization of this for a week that starts on Monday? Saturday (which would be the start of the week in Muslim countries, I believe).
Jonathan Leffler
2009-01-16 05:30:26
+1
A:
Today's day-of-week:
SELECT @dow = DATEPART(d, GETDATE()) where 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.
so you are always adding 8 - today's day-of-week value.
SELECT DATEADD(d, GETDATE(), 8 - @dow(GETDATE))
EDIT: But Cade wins!
le dorfier
2009-01-16 04:29:49
A:
Thanks for the SET DATEFIRST hint, thats just helped me out with a query I had.
Jamie M
2009-09-30 11:24:51