Get the date of a given day in a given week...

Question

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

Answer 1

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.

Answer 2

First:

SET DATEFIRST 7;

...to set the first day of the week for date calculation to be Sunday. Reference: DATEFIRST

To convert the year, month, week & day of week values into a DateTime datatype, use:

CAST(@year +'-'+ 
     @month +'-'+
     @week*6 - DATEPART(dw, @year +'-'+ @month +'-01') + @dayofweek AS DATETIME)

This assumes the data types to be varchar/etc for year & month; week & dayofweek are integer. I leave it to you to cast appropriately if otherwise.

The algorithm for the date is: [week number] * 6 - [the day of the week the 1st of the month falls on] + [day of the week the intended date falls]

Thus:

year: 2009 month: 10 week: 5 day-number: 0

...fills in the algorithm as: 5*6 - 5 + 0 = 25

Answer 3

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