Effective way to set date using week value of month
I have a week of the month in sql and I need to generate a datetime variable from these fields. I have the values dayofweek, weekofmonth and month. does anyone know a quick efficient way to calculate this in mssql 2005? Thanks to
Keep in mind. if the week of the month is set to 5, it is the last week of the month.
Examples: I have DayOfWeek, month, year, week of the month.
so let's say I have DayOfWeek = 2 (Monday) month = 5 (maybe) year = 2009 weekofmonth = 5
I expect that on Monday 25 May 2009
DayOfWeek = 1, Month = 5, Week = 1, Year = 2009 = Sunday, May 3, 2009
DayOfWeek = 5, Month = 4, Week = 3, Year = 2009 = Thursday, April 16, 2009
Additional Information:
I am using the .net TimezoneInfo.TransitionTime class library to help me save 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 defines in which week of the month the time change occurs. Valid values for 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?
a source to share
It's pretty simple, and it really just adds days and weeks on the first day of that month. This assumes you are using the current year and will not be able to handle things like the 5th week of February. (Example: 5th week, 3rd day of February - 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 DayOfWeek == 1 on the first day of the month, so it's almost definitely wrong. Can we see some sample data and expected results?
a source to share
EDIT - changed to handle different values@@datefirst
My guess is based on the available data.
This will work if the SQL Server installation is set @@datefirst
to Sunday (7) or Monday (1).
Test data set to return May 01, 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
a source to share
/*
* 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