Function that finds Sundays

Question

Function that finds Sundays

Viewing 4 posts - 31 through 33 (of 33 total)

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

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

Michael Valentine Jones (11/21/2008)
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?

The OP has been gone from this thread a long time, and never really answered this basic question.

Terri-92562 SSCommitted Points: 1594 More actions · Answer 2

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.]

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

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

Michael Valentine Jones (11/24/2008)
Michael Valentine Jones (11/21/2008)
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?
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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions