asked on

generating a week name from a date

Greetings -

I'm using the code below to generate a date dimension table using a set of variables input by the user. The code fills in the range of dates and also provides useful dimensions like the weekday, etc. The only part that's not working for me uniformly is the week name. The name is supposed to be the Monday of that week, with the week starting Sunday, like this:

2009-03-22 Week of 3/23/2009      Sunday
2009-03-23 Week of 3/23/2009      Monday
2009-03-24 Week of 3/23/2009      Tuesday
2009-03-25 Week of 3/23/2009      Wednesday
2009-03-26 Week of 3/23/2009      Thursday
2009-03-27 Week of 3/23/2009      Friday
2009-03-28 Week of 3/23/2009      Saturday
2009-03-29 Week of 3/30/2009      Sunday

and so on. The code works fine except for weeks which begin in the previous month . . . for these my result is like this:

2009-03-29 Week of 3/30/2009      Sunday
2009-03-30 Week of 3/30/2009      Monday
2009-03-31 Week of 3/30/2009      Tuesday
2009-04-01 Week of 4/30/2009      Wednesday
2009-04-02 Week of 4/30/2009      Thursday
2009-04-03 Week of 4/30/2009      Friday
2009-04-04 Week of 4/30/2009      Saturday

The Week Name for the last four records above should be 'Week of 3/30/2009'

Is there a way to get the last week of the month to describe the week accurately?

UPDATE newseason
SET
	YearNumber = left(@SeasonNumber,4), 
	YearName = left(@SeasonNumber,4),
	SeasonNumber = @SeasonNumber,
	SeasonNumber1 = @SeasonNumber,
	SeasonName = @SeasonName,
	MonthNumber = left(@SeasonNumber,4) + '0' + left(CONVERT(CHAR(8),TID,10),2),
	MonthName = DATENAME(MONTH,TID) ,
 
		WeekName =  REPLACE(('Week of '
					+ left(CONVERT(CHAR(8),TID,10),2) 
					+ '/' 
					+ substring(CONVERT(CHAR(8),(SELECT DATEADD(wk, DATEDIFF(wk,0,TID), 0)),10),4,2) 
					+ '/' + cast(Year(TID) as varchar)),'Week of 0','Week of '),
 
	WeekNumber = cast(Year(TID) as varchar) + '00' + cast(DATEDIFF(week, @FiscalYear, TID-1) as varchar)+1,
 
	DayNumber = cast(Year(TID) as varchar) + '0' + cast(DATEDIFF(day, @FiscalYear, TID+1) as varchar),
 
	DayName = (SELECT DATENAME(dw, TID))

Open in new window

BrandonGalderisi

It would seem as though your "when is Sunday" logic is flawed.

Take a look at this function I posted over at my site which will give you the Sunday of the week that you provide as an input parameter

http://sqlservernation.com/blogs/howtos/archive/2009/04/06/how-to-determine-the-sunday-of-a-given-week.aspx

I suspect you will have similar problems with mid-week year changes like with January 1, 2009 as well.

rsmuckles

ASKER

I'm not shocked that my logic is flawed and I appreciate the example, but I'm not really sure how to apply your date function to my code. The code I'm posting is inherited and I'm no expert at SQL date logic to begin with. Thanks for your help -

Julia

BrandonGalderisi

Well what kind of data is in your "newseason" table?

What values are in TID to start before you run the update above. What is your goal?

rsmuckles

ASKER

The newseason table is a dimension table containing information about a range of dates. The range of dates comprises a season in which products are sold. The fields in the table are things like fiscal week number, season name, and so on, which are used by SQL Server Analytic Services as cube parameters. TID is a datetime field. The strings above that begin with '@' are variables entered in an earlier part of the script by the user. A sample row is in the code box below. I did not include that part of the script as it's simply a set of variable declarations and values. The only one I'm having trouble with is the week name. My goal is to alter the following clause of the update statement:

WeekName = REPLACE(('Week of '
+ left(CONVERT(CHAR(8),TID,10),2)
+ '/'
+ substring(CONVERT(CHAR(8),(SELECT DATEADD(wk, DATEDIFF(wk,0,TID), 0)),10),4,2)
+ '/' + cast(Year(TID) as varchar)),'Week of 0','Week of '),

in such a way that all of the dates in a week which has parts in two different months share the same week name, as in the example in my original post.

2009-03-21 00:00:00.000	2009	2009	200902	200902	Spring-2009	2009003	March	20090032	Week of 3/16/2009	20090223	Saturday

Open in new window

ASKER CERTIFIED SOLUTION

BrandonGalderisi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rsmuckles

ASKER

that's pretty close, although with the attached code it's naming all of the weeks after Saturday's date instead of Monday's, and the WeekName and Weeknumber do not correlate. I'll try and see if I can get it to name the week after Monday instead as it is in the example.

UPDATE BF_OPS_DM.dbo.dimtime_newseason
SET
	YearNumber = left(@SeasonNumber,4), 
	YearName = left(@SeasonNumber,4),
	SeasonNumber = @SeasonNumber,
	SeasonNumber1 = @SeasonNumber,
	SeasonName = @SeasonName,
	MonthNumber = left(@SeasonNumber,4) + '0' + left(CONVERT(CHAR(8),TID,10),2),
	MonthName = DATENAME(MONTH,TID) ,
	WeekName =  'Week of ' + convert(varchar(10), dbo.fn_SundayOfWeek(TID), 101),
	WeekNumber = cast(Year(TID) as varchar) + '00' + cast(DATEDIFF(week, @FiscalYear, TID-1) as varchar)+1,
	DayNumber = cast(Year(TID) as varchar) + '0' + cast(DATEDIFF(day, @FiscalYear, TID+1) as varchar),
	DayName = (SELECT DATENAME(dw, TID))
 
select * from BF_OPS_DM.dbo.dimtime_newseason order by TID

Open in new window

rsmuckles

ASKER

line 10 should be as in the code below for the business rules I have to use.

Thank you so much!

- Julia

WeekName =  'Week of ' + convert(varchar(10), (dbo.fn_SundayOfWeek(TID-2)+1), 101)

Open in new window

rsmuckles

ASKER

thanks again

BrandonGalderisi

The code that I posted:

WeekName = 'Week of ' + convert(varchar(10), dbo.fn_SundayOfWeek(TID), 101)

Will return the "Week Of MM/DD/YYYY" for the SUNDAY of whatever date is passed in.

Let's look at this:

cast(DATEDIFF(week, @FiscalYear, TID-1)

First of all, what value is @FiscalYear. Also, by -1 are you hoping to subtract a week? IF so, you need to move the -1 outside of the (). This is because SomeDateValue-1 = ONE DAY PRIOR (24 hours) to SomeDateValue.

DATEDIFF(week, @FiscalYear, TID)-1