November 21, 2008 at 1:34 pm
select convert(datetime,getdate()) - Datepart(dw, convert(datetime,getdate())) + 8
November 21, 2008 at 1:46 pm
Or more generally, if @weekday represents the weekday that you want to retrieve, where Monday = 1, Tuesday = 2, ..., Sunday = 7, then the following expressions will do the job:
Date of previous weekday as specified in @weekday (will not return today)
SELECT DATEADD(day, (-5 + @weekday - DATEPART(dw, GETDATE()) - @@DATEFIRST) % 7 - 1, GETDATE())
Date of previous weekday as specified in @weekday (may return today)
SELECT DATEADD(day, (-6 + @weekday - DATEPART(dw, GETDATE()) - @@DATEFIRST) % 7, GETDATE())
Date of next weekday as specified in @weekday (may return today)
SELECT DATEADD(day, (15 + @weekday - DATEPART(dw, GETDATE()) - @@DATEFIRST) % 7, GETDATE())
Date of next weekday as specified in @weekday (will not return today)
SELECT DATEADD(day, (14 + @weekday - DATEPART(dw, GETDATE()) - @@DATEFIRST) % 7 + 1, GETDATE())
November 21, 2008 at 3:26 pm
Assuming Sunday is dw=1 on your server:
ALTER FUNCTION dbo.ufNextSunday( @from datetime)
RETURNS datetime
AS
BEGIN
declare @nextSunday datetime
SELECT @nextSunday = @from+N
from tally
where datepart(dw,@from+N) =1
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 21, 2008 at 3:43 pm
Just my opinion, but any function offered here, really should be able to return the same value regardless of setting on indiviual servers. For example, the first solution offered may have worked for the individual who posted the function, but it failed on my system.
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
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
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
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)
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply