June 11, 2003 at 6:24 pm
I was wondering how one would create a stroed procedure to determine the first and last Sunday of any month in any year ? I have been playing with this for a while now with no success.
June 11, 2003 at 6:49 pm
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 ...
June 11, 2003 at 7:00 pm
This will give the first Sunday of the given month of the given year. For the last Sunday do something similar.
DECLARE @month TINYINT
, @year SMALLINT
, @date DATETIME
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
June 11, 2003 at 7:45 pm
Here is the complete solution (I ran a few tests, needs more testing):
DECLARE @month TINYINT
, @year SMALLINT
, @curr_month_date DATETIME
, @next_month_date DATETIME
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
June 13, 2003 at 11:20 am
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 )
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply