Efficent way to set date using week of month value

Question

I have a week of month in sql and i need to generate a datetime varible from that.. fields i have are dayofweek, weekofmonth and month values. anyone know of a quick effective way to calculate this in mssql 2005? Thanks

Keep in mind. if week of month is set to 5 it is the last week of the month

Examples are i have DayOfWeek, month, year, weekofmonth..

so lets say i have DayOfWeek = 2 (monday) month = 5 ( may) year = 2009 weekofmonth = 5

i would expect monday may 25th 2009

DayOfWeek = 1, Month = 5, Week = 1, year = 2009 = Sunday, May 3rd 2009

DayOfWeek = 5, Month = 4, Week = 3, Year = 2009 = Thursday, April 16th 2009

Additional Info:

I am using the .net TimezoneInfo.TransitionTime class library to help me store some stuff in the db.. they say:

The Month property defines the month in which the time change occurs. The Day property defines the day of the week on which the transition occurs. The Week property determines which week of the month the time change occurs in. Valid values of the Week property can range from 1 to 5. A value of 5 indicates the last week of the month.

so far i have this:

declare @IsFixedDateRule    bit
declare @Day    tinyint
declare @DayOfweek   tinyint
declare @Week    tinyint
declare @Month    tinyint
declare @Year    int
declare @TimeofDay   int -- SECONDS

set @IsFixedDateRule = 0
set @Day = 1
set @DayOfweek = 1
set @Week = 5
set @Month = 4
set @year = 2008
set @TimeofDay = 7200

declare @date datetime

if (@IsFixedDateRule = 1) 
begin
 set @date = convert(char(4),@Year) + '/' + convert(char(2),@Month) + '/' + convert(char(2),@Day)  -- Set Year/Month
end
else
begin  
 set @date = convert(char(4),@Year)  + '/' + convert(char(2),@Month) + '/01' -- Set Year/Month

 declare @datepart tinyint
 set @datepart = datepart(weekday ,@date) 

 set @date = dateadd(week, @week - 1, dateadd(weekday, @Dayofweek - case when @datepart = 7 then 0 else @datepart end, @date))-- get the day

end

select dateadd(second, @TimeofDay,  @date) -- Add Time Of Day

anything cleaner?

Answer 1

The first week of May starts on Friday. So what date would be dayofweek=1, weekofmonth=1, month=5? Is that Monday April 27th?

Answer 2

This is fairly simple, and really just adds days and weeks to the first day of that month. It assumes you're using the current year, and won't really handle things like the 5th week of February. (Example: 5th week, 3rd day of February gives 03/11/2009)

DECLARE @CalculatedDate DATETIME;

-- Assuming DayOfWeek and WeekOfMonth are 1-based
SELECT @CalculatedDate = DATEADD(DAY, @DayOfWeek - 1, DATEADD(WEEK, @WeekOfMonth - 1, '2009-' + STR(@Month) + '-01'));

-- Assuming DayOfWeek and WeekOfMonth are 0-based
SELECT @CalculatedDate = DATEADD(DAY, @DayOfWeek, DATEADD(WEEK, @WeekOfMonth, '2009-' + STR(@Month) + '-01'))

It also assumes that DayOfWeek == 1 for the first day of the month, so it's almost definitely wrong. Can we see some sample data and expected outputs?

Answer 3

EDIT - amended to handle different values of @@datefirst

My guess based on the data available so far.

This will work if you SQL server installation has @@datefirst set to Sunday(7) or Monday(1).

The test data is set up to return 01-May-2009

declare @dayofweek int
declare @weekofmonth int
declare @month int
declare @datefirst_adjustment int


select @datefirst_adjustment = case @@datefirst when 1 then 0
                                                when 7 then 1
                               end

declare @firstofyear datetime

set @firstofyear = '20090101'

set @dayofweek = 5

set @month = 5

set @weekofmonth = 1


select dateadd(ww,@weekofmonth - 1,dateadd(mm,@month - 1,@firstofyear)) - datepart(dw,dateadd(mm,@month - 1,@firstofyear)) + @dayofweek + @datefirst_adjustment

Answer 4

/* 
 * Assuming @year is this year
 *          @day_of_week starts from Sunday = 1, Monday = 2 etc
 */
declare @week_of_month int,
        @day_of_week int,
        @month int,
     @year int,
     @derived_date datetime

set dateformat dmy
select @year = datepart(year, getdate()),
       @day_of_week = 2,
       @week_of_month = 2,
       @month = 5


select @derived_date = convert(varchar,@year) + '-01-' + convert(varchar,@month) 
select @derived_date
select @derived_date = dateadd(dd, @day_of_week - datepart(dw, @derived_date), dateadd(week, @week_of_month-1,  @derived_date))
select @year, @month, @day_of_week, @week_of_month, @derived_date

/* Test */
declare @date datetime

set @date = '04-MAY-2009'
select @date, cast(datename(week,@date) as int)- cast( datename(week,dateadd(dd,1-day(@date),@date)) as int)+1 week_of_month, datepart(dw, @date) day_of_weekhere