i need to start at a year-month and work out what the date it is in a given week, on a given day within that week..
year: 2009 month: 10 week: 5 day-number: 0
would return 2009-10-25 00:00:00 which is a sunday. Notice week 5, there is no day 0 in week 5 in 2009-10 as the sunday in that logical week is 2009-11-01 00:00:00... so week 5 would always return the last possible date for the given day in the given month..
if you havn't guessed already i'm messing with the c struct TIME_ZONE_INFORMATION (link text) which is pretty crazy if i'm fair...
Date math and SQL are something to be admired, sadly its something i have never really dug deep into beyond stripping times. Any help would be greatly appriciated.
PS: mssql 2005 btw..
Can you use T-SQL stored procedures? If so, DATEPART would be the function to use.
The weekday (dw) datepart returns a number that corresponds to the day of the week, for example: Sunday = 1, Saturday = 7. The number produced by the weekday datepart depends on the value set by SET DATEFIRST, which sets the first day of the week.
this is my implementation, and it works perfectly. It finds the first occurance of the requested day in SQL datetime (in 1753) and calculates how many days to add to get to the first occurence in our selected year/month. Once we have that we simply case test the result of adding x weeks to that date to ensure it always lands within the selected month.
This is extreamly handy for those of you who have thought about UTC dates:times in an SQL database. If you want to graph 'result-over-time' and that time spans a couple months knowing the daylight-bias and when it commences/ends is invaluble information for your query.
Create function dbo.DST_From_MSStandardDate ( @year int, @month int, @week int, @hour int, -- Sun = 1, Mon = 2, Tue = 3, Wed = 4 -- Thu = 5, Fri = 6, Sat = 7 -- Default to Sunday @day int = 1 ) /* Find the first day matching @day in the month-year requested. Then add the number of weeks to a minimum of start of the month and maximum of end of the month */ returns datetime as begin declare @startPoint datetime declare @finalPoint datetime declare @firstDayInMonth datetime declare @begin_week datetime -- Create the base date set @startPoint = dateadd(mm,(@year-1900)* 12 + @month - 1,0) -- Check for valid day of week if @day between 1 and 7 begin -- Find first day on or after 1753/1/1 (-53690) -- matching day of week of @day select @begin_week = convert(datetime,-53690+((@day+5)%7)) -- Verify beginning of week not before 1753/1/1 if @startPoint >= @begin_week begin select @firstDayInMonth = dateadd(dd,(datediff(dd,@begin_week,@startPoint)/7)*7,@begin_week) end end -- Case for an offset, some weeks have 5 weeks, others have 4 weeks. set @finalPoint = dateadd(hour,@hour,dateadd(wk,@week- case when datepart(month,dateadd(wk,@week,@firstDayInMonth))>@month then 1 -- day lands in the following month when datepart(month,dateadd(wk,@week,@firstDayInMonth))<@month then 0 -- day lands in the proceeding month else 0 end ,@firstDayInMonth)) return @finalPoint end go