Get the date of a given day in a given week

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..

i.e

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..

14.10.2009 00:26:21
You have the year & month - the only calculation you need to perform is on the week & day-number.
OMG Ponies 14.10.2009 14:57:09
2 ОТВЕТА

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.

0
14.10.2009 00:36:18
Yes, SQL as a language is pretty straight forward stuff. The above is fairly advanced, and will require some date mathmatics - well it shouldn't be had MS implemted a proper date:time model.
Sean.C 14.10.2009 00:40:42
Okay, so I'm trying to understand your question... you consider the first week in a month "week 0", is that right? And you basically want to convert "Year=2009, Month=10, Week=2, Day=3" into an actual date? I assume "week 0" can have fewer than 7 days, since we're basically talking about "calendar rows", correct?
bobbymcr 14.10.2009 01:25:14

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
1
14.10.2009 11:40:23