My Techy Memories

The following Named Calculated filed returns Week Start Date & End Date. 'Week ' + Convert(Char(2), WeekNumberOfYear) + '(' + Convert(Char(10),(FullDateAlternateKey - (DATEPART(dw,FullDateAlternateKey)-1)),101) + ' - ' + Convert(Char(10),(FullDateAlternateKey - (DATEPART(dw,FullDateAlternateKey) - 1) + 6),101) + ')' Examples: -- for Date '2008-01-01', Returns Week 1(01/01/2008 - 01/07/2008) -- for Date '2008-09-08', Returns Week 37(09/07/2008 - 09/13/2008) ExcelSheet Formulas for Week Calculation: Date Cell(A2): 09/08/2008 Week Start Date Cell(K2): =DATE(YEAR(A2), MONTH(A2), DAY(A2) - (WEEKDAY(A2) - 1)) -- Returns 09/07/2008 Week End Date Cell(L2): =DATE(YEAR(A2), MONTH(A2), DAY(A2) - (WEEKDAY(A2) - 1) + 6) --Returns 09/13/2008 Start & End Date: =CONCATENATE(YEAR(A2), " - Week ", WEEKNUM(A2), " (", TEXT(K2, "mm/dd/yyyy"), " - ", TEXT(L2, "mm/dd/yyyy"), ")...

Hi, Today I wrote a small SP, which generates AdventureWorks DW DimeTime Table Rows. If you have new Table use the following statement. Usage: Exec GenerateDates '2004-07-01','2008-12-31' Above statement Generates Dates between July, 2004 to Dec, 2008 If you want to run on Exising AdventureWorks Table use the following statement. Usage: Exec GenerateDates '','2009-12-31' Above statement Generates Dates till Dec, 2008 ALTER PROCEDURE [dbo] . [GenerateDates] @Start_Date DateTime , @End_Date DateTime AS DECLARE @Days_To_Insert Integer , @Day_Nbr_of_Week tinyint , @English_Week_Name nvarchar ( 10 ), @Day_Nbr_of_Month tinyint , @Day_Nbr_of_Year smallint , @Week_Nbr_of_Year tinyint , @English_Month_Name nvarchar ( 10 ), @Month_Nbr_of_Year tinyint , @Calendar_Quarter tinyint , @Calendar_Year char ( 4 ), @Calendar_Semester tinyint , @IsValidDate tinyint BEGIN Begin Try SELECT @IsValidDate = IsDate ( @End_Date ) If ( @Is...