Databases
StackOverflow2013
Postsdbo
POEfficent way to set date using week of month value
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POEfficent way to set date using week of month value
    text
    copied!<p>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</p> <p>Keep in mind. if week of month is set to 5 it is the last week of the month</p> <p>Examples are i have DayOfWeek, month, year, weekofmonth..</p> <p>so lets say i have DayOfWeek = 2 (monday) month = 5 ( may) year = 2009 weekofmonth = 5</p> <p>i would expect monday may 25th 2009</p> <p>DayOfWeek = 1, Month = 5, Week = 1, year = 2009 = Sunday, May 3rd 2009</p> <p>DayOfWeek = 5, Month = 4, Week = 3, Year = 2009 = Thursday, April 16th 2009</p> <p>Additional Info:</p> <p>I am using the .net TimezoneInfo.TransitionTime class library to help me store some stuff in the db.. they say: </p> <p><strong>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.</strong></p> <p>so far i have this:</p> <pre><code>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 </code></pre> <p>anything cleaner?</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload