First Sunday and Last Sunday in a Month

Question

Viewing 5 posts - 1 through 4 (of 4 total)

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

NPeeters SSChampion Points: 12536 More actions · Answer 1

Comparable thread some time ago. You have to do some calculation with DATEPART and DATEADD and stuff.

You should use DATEPART(wd, <<first of your month>>) to get the weekday of the first of the month, and use that to calculate the first sunday.

Something along the lines of



if DATEPART(wd, "12/01/2003) = 0

   return "12/01/2003"

else

   return DATEADD(dd, "12/01/2003", 7-DATEPART(wd, "12/01/2003"))

For the last, you do comparable arithmetic but backwards ...

mromm SSCarpal Tunnel Points: 4301 More actions · Answer 2

This will give the first Sunday of the given month of the given year. For the last Sunday do something similar.

DECLARE@monthTINYINT

,@yearSMALLINT

,@dateDATETIME

SELECT@month = 8

,@year = 2003

SET @date = CAST( CAST( @month AS VARCHAR ) + '/01/' + CAST( @year AS VARCHAR ) AS DATETIME )

SELECT@date

,DATEPART ( weekday , @date )

,@date + ( 7 - DATEPART ( weekday , @date ) + 1 ) % 7

mromm SSCarpal Tunnel Points: 4301 More actions · Answer 3

Here is the complete solution (I ran a few tests, needs more testing):

DECLARE@monthTINYINT

,@yearSMALLINT

,@curr_month_dateDATETIME

,@next_month_dateDATETIME

SELECT@month = 7

,@year = 2003

SELECT@curr_month_date =CAST( CAST( @month AS VARCHAR ) +

'/01/' +

CAST( @year AS VARCHAR ) AS DATETIME )

,@next_month_date = CAST( CAST( ( @month + 1 ) % 12 AS VARCHAR ) +

'/01/' +

CAST( @year + 1 - SIGN( 12 - @month ) AS VARCHAR ) AS DATETIME )

SELECT@curr_month_date + ( 7 - DATEPART ( weekday , @curr_month_date ) + 1 ) % 7 AS first_sunday

,@next_month_date - ( DATEPART ( weekday , @next_month_date ) - 1 ) % 7 AS last_sunday

spartanx15 SSC Veteran Points: 216 More actions · Answer 4

MNAY THANKS for the assistance ! Not only does the code sample work for 2003 - 2020 but it was VERY instructive also. I learned a bit more today.

I appreciate the time and effort very much AND the clear explaination.

With much gratitude,

Jon Spartan ( yeah, I know )