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

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.

Can you post some more examples (f.e. what is dayofweek=1, weekofmonth=1, month=5, year=2009). And August has 6 weeks this year... is your 5th week like a maximum? If it is, how would you denote August 31st, so that it's different from August 24th?
–
AndomarMay 12 '09 at 20:29

If the dayofweek=1, weekofmonth=1, month=5 is the 3rd of May, how do you indicate the 1st of May?
–
AndomarMay 13 '09 at 12:33

this example covers only dates that are not on fixed schedules.. fixed dates are handled much cleaner.. this usually handles dates like the 3rd wednesday of april.. ect. in your examples to get the first of may we would need to do.. month = 5 dayofweek = 6 (fri) week = 1 year = 2009
–
BobbyMay 13 '09 at 13:27

4 Answers
4

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)

select @year = datepart(year, getdate()), @day_of_week = 6, @week_of_month = 5, @month = 6 this goes into july.. week 5 needs to be the last week in june.. should be june 26th.. maybe i can check and remove 1 week if the month isnt the same as the one passed in if (datepart(month, @derived_date) <> @month) set @derived_date = dateadd(week, -1, @derived_date)
–
BobbyMay 13 '09 at 13:38