Function that finds Sundays

Question

Post reply

Function that finds Sundays

marty.seed

SSCrazy

Points: 2661
More actions
November 21, 2008 at 10:18 am

#132225

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

Viewing 15 posts - 1 through 15 (of 33 total)

You must be logged in to reply to this topic. Login to reply

Ken Simmons SSCertifiable Points: 7822 More actions · Answer 1

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

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.

Glen SSCertifiable Points: 6737 More actions · Answer 3

Glen

SSCertifiable

Points: 6737

November 21, 2008 at 10:45 am

#901770

select getdate() + 7 - Datepart(dw, getdate())

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

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?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

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.

marty.seed SSCrazy Points: 2661 More actions · Answer 6

Sorry, good question. The next Sunday

So if I was to pass in todays date I would get 11/23/08

Glen SSCertifiable Points: 6737 More actions · Answer 7

Given 2008-11-19 which Sunday do you want, 2008-11-16 or 2008-11-23

Lynn, I hope this is not a question addressed to me? I am not providing interpreter's services 🙂

I just provided a one line solution which requestor can modify accordingly to the missed part of his/her question. 🙂

Use it as an example if it is helpful...

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

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?

Glen SSCertifiable Points: 6737 More actions · Answer 9

Also, on my server here at work, the above returns tomorrow (Saturday), not a Sunday.

You have to check what are the settings on your server?

select @@datefirst

I believe default is 7 i.e. default first day of the week is Sunday. On mine default is set to 1.

Glen SSCertifiable Points: 6737 More actions · Answer 10

But then again, which Sunday does the OP want?

Sorry, good question. The next Sunday

andrewd.smith SSCrazy Eights Points: 8444 More actions · Answer 11

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.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 12

marty.seed (11/21/2008)
Sorry, good question. The next Sunday
So 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?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 13

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())

andrewd.smith SSCrazy Eights Points: 8444 More actions · Answer 14

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())