November 22, 2008 at 3:07 am
SELECTDATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()) / 7 * 7, '18991231') AS previousSunday,
DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()) / 7 * 7, '19000107') AS followingSunday
N 56°04'39.16"
E 12°55'05.25"
November 22, 2008 at 12:34 pm
Here you go, Lynn. It works regardless of the @@datefirst setting.
ALTER FUNCTION dbo.ufNextSunday( @from datetime)
RETURNS datetime
AS
BEGIN
declare @nextSunday datetime
SELECT @nextSunday = @from+N
from tally
where datepart(dw,@from+N) = 8-@@datefirst
and N <=7
RETURN @nextSunday
END
GO
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
November 22, 2008 at 4:04 pm
"KISS" it...
SELECT DATEADD(wk,DATEDIFF(wk,0,somedatetimevalue+7),0)-1
--Jeff Moden
Change is inevitable... Change for the better is not.
November 23, 2008 at 9:27 am
Jeff, that rocks.
But would you please take a little time to explain WHY it works? I will confess to being mystified. I ran the code below to show all the parts of the expression you wrote, and I'm not seeing anything that tells me how this expression always returns a Sunday date. What am I missing?
Thanks
--------------------------------------------------------------------------------------------------------------
declare @test-2 table (date1st int, dwStart int,sunday23rd int,startDt datetime, nextSunday datetime)
declare @x int
declare @start datetime
set @start = '11/21/2008'
set @x = 0
while @x < 7
begin
set @x = @x+1
set datefirst @x
select @start as start, @x as date1st, cast (0 as datetime) as DateZero
,DATEDIFF(wk,0,@start+7) as dateDif
,DATEADD(wk,DATEDIFF(wk,0,@start+7),0)-1
end
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
November 23, 2008 at 5:30 pm
The answer is, what day of the week did date "0" occur on? Then, look at the (-1) and all will become clear.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 24, 2008 at 1:52 am
Jeff's simplified.
SELECT DATEADD(wk,DATEDIFF(wk, 0, getdate()), 6)
However when run on a sunday, the code returns following sunday.
My suggestion returns same sunday if run on a sunday.
N 56°04'39.16"
E 12°55'05.25"
November 24, 2008 at 6:11 am
Peso (11/24/2008)
Jeff's simplified.SELECT DATEADD(wk,DATEDIFF(wk, 0, getdate()), 6)
However when run on a sunday, the code returns following sunday.
My suggestion returns same sunday if run on a sunday.
Heh.. yeah... I did build mine to always return the following Sunday even if the current day is Sunday. I also did the math externally instead of internally because folks have a hard enough time figuring it out. Your way is better because it does eliminate a couple calculations.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 24, 2008 at 7:03 am
Jeff Moden (11/24/2008)
Your way is better because it does eliminate a couple calculations.
Only OP can say which is right. Better is a rather subjective term.. 🙂
SELECT DATEADD(wk,DATEDIFF(wk, '19000101', getdate()), '19000107')
N 56°04'39.16"
E 12°55'05.25"
November 24, 2008 at 8:04 am
SELECT DATEADD(wk,DATEDIFF(wk, 0, getdate()), 6)
Yes, this works well for Sundays, but it is not as straightforward as you would hope to generalise it to work for other days of the week.
For instance, you might hope that the following would be the equivalent expression to return the next Saturday:
SELECT DATEADD(wk,DATEDIFF(wk, 0, getdate()), 5)
The above works for all days of the week except for Saturday, but if today is a Saturday, it returns today rather than the following Saturday, whereas the following will always return the next Saturday even if today is a Saturday, and the expression works for all days of the week (by changing value of @weekday).
DECLARE @weekday int
SELECT @weekday = 6 /* Saturday */
SELECT DATEADD(day, (14 + @weekday - DATEPART(dw, @dt) - @@DATEFIRST) % 7 + 1, @dt)
November 24, 2008 at 8:08 am
SELECTDATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()) / 7 * 7, '19000101') AS followingMonday,
DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()) / 7 * 7, '19000102') AS followingTuesday,
DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()) / 7 * 7, '19000103') AS followingWednesday,
DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()) / 7 * 7, '19000104') AS followingThursday,
DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()) / 7 * 7, '19000105') AS followingFriday,
DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()) / 7 * 7, '19000106') AS followingSaturday,
DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()) / 7 * 7, '19000107') AS followingSunday
N 56°04'39.16"
E 12°55'05.25"
November 24, 2008 at 8:20 am
SELECT DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()) / 7 * 7, '19000101') AS followingMonday,
DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()) / 7 * 7, '19000102') AS followingTuesday,
DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()) / 7 * 7, '19000103') AS followingWednesday,
DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()) / 7 * 7, '19000104') AS followingThursday,
DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()) / 7 * 7, '19000105') AS followingFriday,
DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()) / 7 * 7, '19000106') AS followingSaturday,
DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()) / 7 * 7, '19000107') AS followingSunday
I'm sorry, but these expressions don't reliably return the following weekday.
e.g. the following does return a Saturday, but it is the previous Saturday, not the next Saturday.
SELECT DATEADD(DAY, DATEDIFF(DAY, '19000101', '2008-11-23') / 7 * 7, '19000106')
------------------------------------------------------
2008-11-22 00:00:00.000
(1 row(s) affected)
November 24, 2008 at 8:25 am
Michael Valentine Jones (11/21/2008)
marty.seed (11/21/2008)
Sorry, good question. The next SundaySo if I was to pass in todays date I would get 11/23/08
What do you want it to return if today is Sunday, today or 7 days later?
The OP has been gone from this thread a long time, and never really answered this basic question.
November 24, 2008 at 9:16 am
andrewd.smith (11/24/2008)
SELECT DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()) / 7 * 7, '19000101') AS followingMonday,DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()) / 7 * 7, '19000102') AS followingTuesday,
DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()) / 7 * 7, '19000103') AS followingWednesday,
DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()) / 7 * 7, '19000104') AS followingThursday,
DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()) / 7 * 7, '19000105') AS followingFriday,
DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()) / 7 * 7, '19000106') AS followingSaturday,
DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()) / 7 * 7, '19000107') AS followingSunday
I'm sorry, but these expressions don't reliably return the following weekday.
e.g. the following does return a Saturday, but it is the previous Saturday, not the next Saturday.
SELECT DATEADD(DAY, DATEDIFF(DAY, '19000101', '2008-11-23') / 7 * 7, '19000106')------------------------------------------------------
2008-11-22 00:00:00.000
(1 row(s) affected)
Try this:
-- based on 1/1/1900 = 0 is a Monday
PRINT GETDATE()
PRINT 'Next occurance of Monday: '
PRINT DATEADD(day, DATEDIFF(day,0,GETDATE()-1)/7*7+7, 0) -- use 1/1/1900 = 0 is a Monday
PRINT 'Next occurance of Tuesday: '
PRINT DATEADD(day, DATEDIFF(day,1,GETDATE()-1)/7*7+7, 1) -- use 1/2/1900 = 1 is a Tuesday
PRINT 'Next occurance of Wednesday: '
PRINT DATEADD(day, DATEDIFF(day,2,GETDATE()-1)/7*7+7, 2) -- use 1/3/1900 = 2 is a Wednesday
PRINT 'Next occurance of Thursday: '
PRINT DATEADD(day, DATEDIFF(day,3,GETDATE()-1)/7*7+7, 3) -- use 1/4/1900 = 3 is a Thursday
PRINT 'Next occurance of Friday: '
PRINT DATEADD(day, DATEDIFF(day,4,GETDATE()-1)/7*7+7, 4) -- use 1/5/1900 = 4 is a Friday
PRINT 'Next occurance of Saturday: '
PRINT DATEADD(day, DATEDIFF(day,5,GETDATE()-1)/7*7+7, 5) -- use 1/6/1900 = 5 is a Saturday
PRINT 'Next occurance of Sunday: '
PRINT DATEADD(day, DATEDIFF(day,6,GETDATE()-1)/7*7+7, 6) -- use 1/7/1900 = 6 is a Sunday
Terri
To speak algebraically, Mr. M. is execrable, but Mr. C. is
(x+1)-ecrable.
Edgar Allan Poe
[Discussing fellow writers Cornelius Mathews and William Ellery Channing.]
November 24, 2008 at 10:09 am
Try this:
-- based on 1/1/1900 = 0 is a Monday
PRINT GETDATE()
PRINT 'Next occurance of Monday: '
PRINT DATEADD(day, DATEDIFF(day,0,GETDATE()-1)/7*7+7, 0) -- use 1/1/1900 = 0 is a Monday
PRINT 'Next occurance of Tuesday: '
PRINT DATEADD(day, DATEDIFF(day,1,GETDATE()-1)/7*7+7, 1) -- use 1/2/1900 = 1 is a Tuesday
PRINT 'Next occurance of Wednesday: '
PRINT DATEADD(day, DATEDIFF(day,2,GETDATE()-1)/7*7+7, 2) -- use 1/3/1900 = 2 is a Wednesday
PRINT 'Next occurance of Thursday: '
PRINT DATEADD(day, DATEDIFF(day,3,GETDATE()-1)/7*7+7, 3) -- use 1/4/1900 = 3 is a Thursday
PRINT 'Next occurance of Friday: '
PRINT DATEADD(day, DATEDIFF(day,4,GETDATE()-1)/7*7+7, 4) -- use 1/5/1900 = 4 is a Friday
PRINT 'Next occurance of Saturday: '
PRINT DATEADD(day, DATEDIFF(day,5,GETDATE()-1)/7*7+7, 5) -- use 1/6/1900 = 5 is a Saturday
PRINT 'Next occurance of Sunday: '
PRINT DATEADD(day, DATEDIFF(day,6,GETDATE()-1)/7*7+7, 6) -- use 1/7/1900 = 6 is a Sunday
Yes, these expressions are ok if you want to return today rather than 7 days in the future if today is the specified weekday.
The following 3 expressions are almost equivalent in their behaviour. The 3rd expression is a generalisation of the those above. The only significant difference is that the 3rd expression loses any time information that may be present. The 1st and 2nd expressions below are equivalent in their output, but the 2nd expression avoids the use of the DATEPART and @@DATEFIRST functions, though it does require an extra modulo operator.
DECLARE @weekday int
SELECT @weekday = 7 /* Monday = 1, Tuesday = 2, ..., Sunday = 7 */
SELECT DATEADD(day, (@weekday - DATEPART(dw, GETDATE()) - @@DATEFIRST + 15) % 7, GETDATE())
SELECT DATEADD(day, (@weekday - DATEDIFF(day, 0, GETDATE()) % 7 + 6) % 7, GETDATE())
SELECT DATEADD(day, DATEDIFF(day, (@weekday - 1), GETDATE() - 1) / 7 * 7 + 7, (@weekday - 1))
The following are the analogous 3 expressions if you don't want to return today if today is the day of the week specified in @weekday.
DECLARE @weekday int
SELECT @weekday = 7 /* Monday = 1, Tuesday = 2, ..., Sunday = 7 */
SELECT DATEADD(day, (@weekday - DATEPART(dw, GETDATE()) - @@DATEFIRST + 14) % 7 + 1, GETDATE())
SELECT DATEADD(day, (@weekday - DATEDIFF(day, 0, GETDATE()) % 7 + 5) % 7 + 1, GETDATE())
SELECT DATEADD(day, DATEDIFF(day, (@weekday - 1), GETDATE()) / 7 * 7 + 7, (@weekday - 1))
November 24, 2008 at 9:11 pm
Michael Valentine Jones (11/24/2008)
Michael Valentine Jones (11/21/2008)
marty.seed (11/21/2008)
Sorry, good question. The next SundaySo if I was to pass in todays date I would get 11/23/08
What do you want it to return if today is Sunday, today or 7 days later?
The OP has been gone from this thread a long time, and never really answered this basic question.
Heh... yeah I noticed too... Ok... everyone out of the bus. Tell your parent's we had a good time even if you didn't. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply