March 5, 2009 at 3:39 am
Hello,
I've written a function which populates a table with all the year, month and week values between two years (the week values are the week of the month, e.g. weeks 1 to 5 of Jan, weeks 1 to 5 of Feb, etc). I also have a function that creates a date if you pass it the date parts - however, it needs the year, month and day values.
So, what I want to do is create a complete date for each row in my populated date table. My question is, how can I get the first (or last, or any, to be honest!) day for each week? What I'd ideally like is a function that I can pass a year, month and week value to, and it will return the first date in that week.
Any help would be much appreciated!
Many thanks,
Peter
March 5, 2009 at 4:25 am
How are you defining the start and end of a week and how are you defining week numbers?
For instance, if your week runs from Sunday to Saturday, if the 1st of a month is a Saturday, is that week 1 of the month even though it contains only 1 day?
March 5, 2009 at 4:34 am
That's exactly right. For example, for January 2009 week 1 was Thu 1st, Fri 2nd and Sat 3rd, week 2 was the 4th to the 10th, etc.
March 5, 2009 at 4:42 am
Ah - nevermind, thanks for the help but I've just found a solution! 🙂
I wrote a function which steps through the days of the month until it hits the given week. Thus that date is the first day of the week.
Seems stupidly simple now (a bit like me today ;))!
March 5, 2009 at 5:45 am
How about this.
By setting @firstWeekday, the code can be made to work for any day being the first day of the week, or you can set it to @@DATEFIRST for the default setting for the connection.
If you specify a value of @week that is invalid for the specified @month (e.g. @year=2009, @month=2, @week=5), then the code will return NULL.
DECLARE @year int
DECLARE @month int
DECLARE @week int
DECLARE @firstWeekday int
DECLARE @firstOfMonth datetime
DECLARE @startOfWeek datetime
SELECT @firstWeekday = 7 --@@DATEFIRST
SELECT @year = 2009, @month = 4, @week = 2
SELECT @firstOfMonth = DATEADD(month, (@month - 1), DATEADD(year, (@year - 1900), 0))
SELECT @startOfWeek = CASE
WHEN (@week = 1) THEN @firstOfMonth
ELSE DATEADD(week, DATEDIFF(week, 0, @firstOfMonth - @firstWeekday) + (@week - 1), @firstWeekday - 1) END
SELECT StartOfWeek = CASE WHEN (@month = MONTH(@startOfWeek)) THEN @startOfWeek ELSE NULL END
March 5, 2009 at 5:51 am
peter.marshall (3/5/2009)
Ah - nevermind, thanks for the help but I've just found a solution! 🙂I wrote a function which steps through the days of the month until it hits the given week. Thus that date is the first day of the week.
Seems stupidly simple now (a bit like me today ;))!
Here's an alternative:
DECLARE @year INT, @week int
SELECT @year = 2009, @week = 10
DECLARE @Week1Day1 DATETIME, @Week1Dayx DATETIME
SET @Week1Dayx = STR(@year,4) + '-01-04'
SET @Week1Day1 = @Week1Dayx - ISNULL(NULLIF(DATEDIFF(dd, 0, @Week1Dayx) % 7, 0), 7)
SELECT @Week1Day1 -- monday of week 1 of this year
SELECT DATEADD(wk, @week-1, @Week1Day1) AS MondayOfChosenWeek
The calculation picks up the date of monday of the week which includes January 4th of the chosen year, which is a loose definition of the ISO week[/url]. Then simply add the correct number of weeks to it using DATEADD to yield the date of monday of the desired week. You don't need the month if you have the week number.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply