Solved

generating a week name from a date

Posted on 2009-04-08
9
1,040 Views
Last Modified: 2012-06-27
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

0
Comment
Question by:rsmuckles
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24099944
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.
0
 

Author Comment

by:rsmuckles
ID: 24100667
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
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24100696
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?
0
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

Author Comment

by:rsmuckles
ID: 24100789
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

0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 500 total points
ID: 24100826
What about this?  It will always use the date for the SUNDAY preceeding TID and format it into MM/DD/YYYY.

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

Author Comment

by:rsmuckles
ID: 24100968
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

0
 

Author Comment

by:rsmuckles
ID: 24101052
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

0
 

Author Closing Comment

by:rsmuckles
ID: 31568159
thanks again
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24101069
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

0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question