November 21, 2008 at 10:18 am
Hey there group,
I need to build a function that takes in a date and then returns the corresponding Sunday to that date.
Any ideas?
Marty
November 21, 2008 at 10:31 am
You can do something like this.
Declare @Date datetime
Set @Date = getdate()
Select @Date -
CASE datename(weekday,@Date)
WHEN 'Monday' THEN 1
WHEN 'Tuesday' THEN 2
WHEN 'Wednesday' THEN 3
WHEN 'Thursday' THEN 4
WHEN 'Friday' THEN 5
WHEN 'Saturday' THEN 6
WHEN 'Sunday' THEN 0
END
November 21, 2008 at 10:36 am
marty.seed (11/21/2008)
Hey there group,I need to build a function that takes in a date and then returns the corresponding Sunday to that date.
Any ideas?
Marty
Could you please provide some examples of what your are looking for? I wouldn't know which Sunday date you'd want for a given date just based on your question.
November 21, 2008 at 10:57 am
Glen (11/21/2008)
select getdate() + 7 - Datepart(dw, getdate())
Given 2008-11-19 which Sunday do you want, 2008-11-16 or 2008-11-23?
November 21, 2008 at 11:00 am
Glen (11/21/2008)
select getdate() + 7 - Datepart(dw, getdate())
Also, on my server here at work, the above returns tomorrow (Saturday), not a Sunday.
November 21, 2008 at 11:01 am
Sorry, good question. The next Sunday
So if I was to pass in todays date I would get 11/23/08
November 21, 2008 at 11:09 am
Directed more to the OP, but based on your suggestion, which didn't work on my servers here at work. It returns the Saturday following the given date, not Sunday. But then again, which Sunday does the OP want?
November 21, 2008 at 11:30 am
select getdate() + 7 - Datepart(dw, getdate())
This only works if @@DATEFIRST = 1 (Monday)
An expression that returns the date of the Sunday in the current week regardless of the value of @@DATEFIRST is as follows:
SELECT DATEADD(day, (1 - DATEPART(dw, GETDATE())) + (7 - @@DATEFIRST), GETDATE())
The Sunday that is returned could be before or after the current date depending on the value of @@DATEFIRST, so if you need the previous Sunday or the next Sunday then you need a different expression.
November 21, 2008 at 12:04 pm
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?
November 21, 2008 at 12:04 pm
andrewd.smith (11/21/2008)
select getdate() + 7 - Datepart(dw, getdate())
This only works if @@DATEFIRST = 1 (Monday)
An expression that returns the date of the Sunday in the current week regardless of the value of @@DATEFIRST is as follows:
SELECT DATEADD(day, (1 - DATEPART(dw, GETDATE())) + (7 - @@DATEFIRST), GETDATE())The Sunday that is returned could be before or after the current date depending on the value of @@DATEFIRST, so if you need the previous Sunday or the next Sunday then you need a different expression.
I think, someone else will need to test, that this is what is needed to get the Sunday following the date given:
SELECT DATEADD(day, (8 - DATEPART(dw, GETDATE())) + (7 - @@DATEFIRST), GETDATE())
November 21, 2008 at 1:13 pm
The following returns the date that is the next Sunday. If today is Sunday, it returns today.
SELECT DATEADD(day, (15 - DATEPART(dw, GETDATE()) - @@DATEFIRST) % 7, GETDATE())
The following returns the date that is the next Sunday. If today is Sunday, it does not return today.
SELECT DATEADD(day, (14 - DATEPART(dw, GETDATE()) - @@DATEFIRST) % 7 + 1, GETDATE())
The following returns the date that is the previous Sunday. If today is Sunday, it returns today.
SELECT DATEADD(day, (1 - DATEPART(dw, GETDATE()) - @@DATEFIRST) % 7, GETDATE())
The following returns the date that is the previous Sunday. If today is Sunday, it does not return today.
SELECT DATEADD(day, (2 - DATEPART(dw, GETDATE()) - @@DATEFIRST) % 7 - 1, GETDATE())
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.
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply