August 7, 2008 at 9:28 am
So I've got a week number and year. With that, how can I list the first and last day/date of that week/year.
For example, I already have:
week = 18
Year = 2008
From that I'd like to return:
First day = 2008-04-27
Last day = 2008-05-03
TIA,
-JB
August 7, 2008 at 10:33 am
I recently had to perform this same function to convert data that I was storing by week back into dates for reporting.
Here's what I came up with:
DECLARE @Week int,
@Year int,
@Date datetime
-- SET UP THE WEEK AND THE YEAR
SET@Week = 18
SET@Year = 2008
-- SET THE DATE EQUAL TO
-- JANUARY FIRST OF THE YEAR
-- PLUS WEEK - 1
SET@Date = DATEADD(wk,@Week - 1,CAST('01/01/' + cast(@Year as char(4)) as datetime))
-- THE ASSUMPTION IS WEEK 1 STARTS
-- ON THE FIRST SUNDAY OF THE YEAR
-- AND DATE FIRST IS A SUNDA?Y
SELECTDATEADD(d,(-1) * (DATEPART(dw,@Date) - 1), @Date) as FirstDayOfWeek,
DATEADD(d,7 - DATEPART(dw,@Date), @Date) as LastDayOfWeek
August 7, 2008 at 11:20 am
I think this almost works although for me week 18 would be 5/4-5/10. How do you know that week 18 is 4/27-5/3?
[font="Courier New"]SELECT
DATEADD(week, 18, '1/1/2008') AS date, -- this returns the day the year started on in 2008 tuesday
-- first day Sunday assuming that DateFirst setting is Sunday
DATEADD(week, 18, '1/1/2008') - DATEPART(weekday, DATEADD(week, 18, '1/1/2008')) +1 AS first_day_of_the_week,
-- last day Saturday assuming that DateFirst setting is Sunday
DATEADD(week, 18, '1/1/2008') + (7-DATEPART(weekday, DATEADD(week, 18, '1/1/2008'))) AS last_day_of_the_week[/font]
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 7, 2008 at 11:40 am
4/27 is a Sunday. I just used DATEPART/week to determine the week of that date.
Select datepart(week,'2008-04-27')
August 7, 2008 at 11:42 am
Jack, I believe you are correct. I should not subtract 1 from the week. If I do, then Jan 1 - 5 technically would not have a week the way I coded it.
January 6 begins week 2.
The adjusted SQL is:
DECLARE @Week int,
@Year int,
@Date datetime
-- SET UP THE WEEK AND THE YEAR
SET@Week = 18
SET@Year = 2008
-- SET THE DATE EQUAL TO
-- JANUARY FIRST OF THE YEAR
-- ADD THE NUMBER OF WEEKS
-- NOTE, WEEK 1 MIGHT NOT HAVE
-- 7 DAYS
SET@Date = DATEADD(wk,@Week ,CAST('01/01/' + cast(@Year as char(4)) as datetime))
-- THE ASSUMPTION IS WEEK 1 STARTS
-- ON THE FIRST SUNDAY OF THE YEAR
-- AND DATE FIRST IS A SUNDAY
SELECTDATEADD(d,(-1) * (DATEPART(dw,@Date) - 1), @Date) as FirstDayOfWeek,
DATEADD(d,7 - DATEPART(dw,@Date), @Date) as LastDayOfWeek
August 7, 2008 at 11:57 am
I don't know Buzz. If you do a datepart/week on your starting day it returns week 19.
Which makes sense becuase the @Date variable is our week 18 plus week 1.
Subracting 1 from the @Week variable seems to do the trick on the @Date. I get 4/27 with week 18 and 12/30/2007 for week 1.
SET @Date = DATEADD(wk,@Week-1,CAST('01/01/' + cast(@Year as char(4)) as datetime))
The SELECT statement is fantastic. Thank you for that.
August 7, 2008 at 12:01 pm
You're right John.
In my haste, I again did not account for the first few days of the year if January 1 does not fall on a Sunday.
Please see:
DECLARE @Week int,
@Year int,
@Date datetime
-- SET UP THE WEEK AND THE YEAR
SET@Week = 18
SET@Year = 2008
-- SET THE DATE EQUAL TO
-- JANUARY FIRST OF THE YEAR
-- ADD THE NUMBER OF WEEKS
-- NOTE, WEEK 1 MIGHT NOT HAVE
-- 7 DAYS
SET@Date = CAST('01/01/' + cast(@Year as char(4)) as datetime)
IF DATEPART(dw,@Date) != 1
SET@Week = @Week - 1
SET@Date = DATEADD(wk,@Week, @Date)
-- THE ASSUMPTION IS WEEK 1 STARTS
-- ON THE FIRST SUNDAY OF THE YEAR
-- AND DATE FIRST IS A SUNDAY
SELECTDATEADD(d,(-1) * (DATEPART(dw,@Date) - 1), @Date) as FirstDayOfWeek,
DATEADD(d,7 - DATEPART(dw,@Date), @Date) as LastDayOfWeek
Is it still Monday?
August 7, 2008 at 12:33 pm
I think the issue is that we are all thinking of a week being Sunday - Saturday and SQL Server in returning Week is considering a week being the first day of the year as the the beginning of the week for ALL weeks of that year so in 2008 the DatePart(week, date) the weeks run from Tuesday - Monday.
So the question is do you want to show week 1 of 2008 being Tuesday - Saturday (1/1-1/5) and the rest of the year to run Sunday - Saturday?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 7, 2008 at 12:52 pm
So the question is do you want to show week 1 of 2008 being Tuesday - Saturday (1/1-1/5) and the rest of the year to run Sunday - Saturday?
Yes! And it appears to be working that way already. Run this chunk:
Select DatePart(wk,'2008-01-01') 'A Tuesday'
,DatePart(wk,'2008-01-06') 'The first Sunday'
So, 1/1 is week one (of course), but week 2 starts on the first Sunday, not Monday.
February 1, 2009 at 7:06 am
Hi,
I have been googling for an easy solution of this problem and could not find anything. In the end I strained myself and created two functions.
Here is the code:
CREATE FUNCTION dbo.WFDAY (
@Year int,
@Week int)
RETURNS datetime
AS
BEGIN
DECLARE @YearFirstDay int
DECLARE @WeekFirstDay datetime
SET @YearFirstDay = DATEPART(dw,CONVERT(datetime, CONVERT(char(4), @Year)+'-01-01'))
SET @WeekFirstDay = DATEADD(dd, 7 * @Week - @YearFirstDay - 6, CONVERT(datetime, CONVERT(char(4), @Year)+'-01-01'))
RETURN (@WeekFirstDay)
END
GO
CREATE FUNCTION dbo.WLDAY (
@Year int,
@Week int)
RETURNS datetime
AS
BEGIN
DECLARE @YearFirstDay int
DECLARE @WeekLastDay datetime
SET @YearFirstDay = DATEPART(dw,CONVERT(datetime, CONVERT(char(4), @Year)+'-01-01'))
SET @WeekLastDay = DATEADD(dd, 7 * @Week - @YearFirstDay, CONVERT(datetime, CONVERT(char(4), @Year)+'-01-01'))
RETURN (@WeekLastDay)
END
GO
When functions are created I get the dates by supplying the year and week numbers
SET DATEFIRST 1
SELECT
dbo.WFDAY(2008,52)
The SET DATEFIRST stays for weeks starting on Monday, I guess you do not have to write anything when your first day is Sunday.
I hope it may help you or somebody else
Lida
February 1, 2009 at 9:06 am
I'd suggest the creation of a Calendar table. It has many uses including this one.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 1, 2009 at 10:50 am
I agree. I have a slew of date functions that calculated dates. In the end I just used them to put the dates in a calendar table (I call it PeriodDate) that contains Days, Weeks, Months, Quarters etc information. The nice thing also about a Calendar table is you can use it define custom periods.
Jeff Moden: I used your tally table concept to populate the Calendar table. This prevented me from looping. Thanks for that.
February 1, 2009 at 7:02 pm
Thanks for the feedback, Buzz. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
February 2, 2009 at 3:37 pm
Jeff Moden (2/1/2009)
I'd suggest the creation of a Calendar table. It has many uses including this one.
[font="Verdana"]Wot 'e said.
My Calendar table is called (surprisingly) Calendar. It includes Year, Month, Week, Day numbers. It also includes a date form of the week-ending date (as defined by the business: the last day of the week can differ depending on who you talk to.) So instead of having to calculate this stuff over and over again, you calculate it once when you build the table, and then just look it up. Much easier!
Some additional info on this concept can be found in Joe Celko's article: Temporal Data Techniques in SQL [/url].
All in all, a Calendar table is a handy thing to have around on a permanent basis.
[/font]
February 2, 2009 at 4:35 pm
A calendar table may well be the way to go here, but you will still need a method of calculating dates from week numbers in order to populate the calendar table.
Week numbers are a little complicated because there doesn't seem to be a consensus on how to specify which is the first week of the year. There are two parameters that can vary:
1) The weekday considered to be the first in a week. This is usually either Sunday or Monday.
2) The minimum number of days in the week containing 1st January that are from the new calendar year in order to be considered week #1. This parameter can potentially vary from 1 to 7.
In the first commercial software project that I was involved with in the mid 90's, I had to deal with horticultural week numbers. Horticultural weeks start on Mondays, and week #1 must contain at least 4 days from the new calendar year. Therefore, if New Year's Day is a Thursday then that week contains 4 days from the new calendar year and should be considered week #1, despite the week having started on Monday 29th December of the previous year. However, if New Year's day is a Friday, then that week contains only 3 days from the new calendar year and should be considered the last week (#52 or #53) of the old year. In this case horticultural week #1 of the new year starts on Monday 4th January.
The following function can be used to calculate dates from week numbers for different methods of determining week numbers (by changing the parameters @FirstDayOfWeek and @MinDaysInWeek):
CREATE FUNCTION dbo.WeekNumberToDate (@year int, @week int, @weekday int)
RETURNS datetime
AS BEGIN
/* @year is the full (4-digit) year
@week is the week number (1 - 52 or 53)
@weekday is the ordinal day of the week (1 - 7) */
/* First day of week: Mon=1, Tue=2, Wed=3, Thu=4, Fri=5, Sat=6, Sun=7 */
DECLARE @FirstDayOfWeek int
SELECT @FirstDayOfWeek = 1
/* Minimum number of days from new calendar year in week #1 */
DECLARE @MinDaysInWeek int
SELECT @MinDaysInWeek = 4
DECLARE @y0 int
DECLARE @d0 int
SELECT @y0 = @year - 1
SELECT @d0 = 7 - (8 - @FirstDayOfWeek + @y0 + (@y0 / 4) - (@y0 / 100) + (@y0 / 400)) % 7
RETURN DATEADD(day,
(CASE WHEN (@d0 >= @MinDaysInWeek) THEN @d0 - 7 ELSE @d0 END)
+ (@week - 1) * 7 + (@weekday - 1), DATEADD(year, @year - 1900, 0))
END
The SQL Server function DATEPART(wk, @dt) always returns 1 if @dt is New Year's Day. This behaviour is equivalent to setting @MinDaysInWeek = 1 in the above function, i.e. even if 1st January is the last day of the week, that week is considered week #1 of the new year.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply