Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
Our current date system divides the business year into 'Periods' which last for 4 weeks. Period one begins on a fixed date, lets say 1st March.
I have a simple table with Weeks and which period they come under. I would like to add another column [Date] with each day in it, effectively multiplying my number of rows by 7. How can I go about calculating these values using an inbuilt calender in SSMS?
There isn't a built-in date table for SQL Server, but it's easy to add or expand your own. If you want to add weeks to your existing table, the code might look like this:
UPDATE tblDates
SET WeekOfYear = DATEPART(WEEK, [date])
If you're just starting out and only have dates so far, it may be easier to find someone else's table script and use that. That way, you don't have to write the code to populate all those other columns.
A good example of a simple table using calendar years is here: https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/
This is important: Weeks are goofy -- you'll notice in the script linked above that there's a [week] and there's an [ISO_week] in the DATEPART() functions. Note that the [week] column won't always have seven days (e.g, the first week of January 2013 is only five days long -- Tuesday through Saturday). This is what you'll find with the DATEPART(WEEK, [date]) function. If you need each week in your 4-week period to be exactly 28 days, it's easier to use the ISO_week than to string weeks together in code.
I would suggest dropping the calendar table in favor of a function, like:
CREATE FUNCTION getperiod ( @indate AS date)
RETURNS int
AS
BEGIN
RETURN DATEDIFF(day,'2016-03-01',@indate) / 28;
END;
(Please adapt to your requirements.)
