September 22, 2005 at 5:04 am
Hi,
I have a situation where I select month and year from two separate dropdown lists, then I need to populate a 3rd dropdown list with the list of sundays present in that month. Can anyone help? A quick response is appreciated.
Thanks in advance.
-smitha
September 22, 2005 at 8:15 am
You could also very well do this in vb... just replace the select with a while Month(date) = me.cboMonth
DECLARE @Sunday AS SMALLDATETIME
DECLARE @Year AS INT
DECLARE @Month AS INT
DECLARE @FirstSunday AS SMALLDATETIME
SET @Sunday = '2005/05/01'
SET @Year = 2004
SET @Month = 10
SET @FirstSunday = DATEADD(MM, @Month - 1, DATEADD(YY, @Year - 1900, 0))
SET @FirstSunday = DATEADD(D, 7 - DATEPART(DW, @FirstSunday), @FirstSunday)
Select DATEADD(D, dtSundays.Days, @FirstSunday) AS Sundays from (
SELECT 0 AS Days
UNION ALL
SELECT 7 AS Days
UNION ALL
SELECT 14 AS Days
UNION ALL
SELECT 21 AS Days
UNION ALL
SELECT 28 AS Days
) dtSundays
WHERE MONTH(DATEADD(D, dtSundays.Days, @FirstSunday)) = @Month
--or with a calendar table :
Select Date from dbo.Calendar where Date BETWEEN @DateStart and @DateEnd AND DATEPART(DW, Date) = 7
September 22, 2005 at 8:23 am
Here is a proc that will do it. No hard coded values.
You give it first day of the month.
create proc up_Return_Sundays (@start_date datetime)
as
declare @Sunday_Date table (Sunday_Date int)
SELECT @start_date = DATEADD(day, -1, @start_date)
declare @end_date datetime
SELECT @end_date = DATEADD(day, 1, @start_date)
declare @Av_Day int
declare @my_cnt int
set @my_cnt = 0
declare @month_delta int
WHILE @my_cnt < 31
BEGIN
set @my_cnt = @my_cnt + 1
select @Av_Day = DATEPART ( dw , @end_date )
IF @Av_Day = 1
insert into @Sunday_Date select DATEPART ( day , @end_date )
SELECT @end_date = DATEADD(day, 1, @end_date)
END
select Sunday_Date from @Sunday_Date order by Sunday_Date
--now test it
declare @start_date datetime
set @start_date = '2005/09/01'
exec up_Return_Sundays @start_date
September 22, 2005 at 8:37 am
What's wrong with the hardcoded values in my demo script??
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply