SQL Server - determining what week i'm in

Asked By rodchar

03-Feb-10 09:31 AM

Hi All,

Given a date could I determine what week I am in starting with the current
week, then week1, week2, week3

Thanks,
rodchar

03-Feb-10 09:51 AM

Peter has great article for the subject
http://weblogs.sqlteam.com/peterl/archive/2009/06/17/How-to-get-the-Nth-weekday-of-a-month.aspx

CREATE FUNCTION dbo.WeekOfMonth(@now_day DATETIME)
RETURNS INT
AS
BEGIN
RETURN DATEPART(week, @now_day)
- DATEPART(week, CONVERT(CHAR(6), @now_day, 112)+'01')
+ 1
END
GO

SET DATEFIRST 1
SELECT dbo.WeekOfMonth('20090601') AS [1st]

03-Feb-10 09:59 AM

currentweek, week1ago, week2ago, week3ago

03-Feb-10 09:59 AM

You can use DATEPART to get the week number. The week number is scoped to
the year, so this tells you what week you are in this year.   For example:
SELECT  DATEPART (wk, GETDATE())

Since DATEDIFF counts week boundaries crossed (not durations of 7 days) ,
you can use DATEDIFF to establish a week number over a greater period of
time, such as:
SELECT DATEDIFF(wk,'1900/1/1',GETDATE())

To get the week number difference from today with another date, you could do
something like:
SELECT DATEDIFF(wk,'1900/1/1', '2012/12/31') -
DATEDIFF(wk,'1900/1/1',GETDATE())

The week boundary calculation is affected by SET DATEFIRST.

These commands are all in the Books Online, so you can read more about them.
http://msdn.microsoft.com/en-us/library/ms174420.aspx
http://msdn.microsoft.com/en-us/library/ms189794.aspx
http://msdn.microsoft.com/en-us/library/ms181598.aspx

RLF

03-Feb-10 10:00 AM

Not sure I understand, but you can use the date/time functions to get the week:

SELECT DATEPART(WEEK, CURRENT_TIMESTAMP);

Or if ISO week is needed (SQL Server 2008 only):

SELECT DATEPART(ISO_WEEK, CURRENT_TIMESTAMP);

The alternative is to have a calendar table and match the week from there:
http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

--
Plamen Ratchev
http://www.SQLStudio.com

03-Feb-10 10:55 AM

Dates are tricky from a definitional perspective; I think what others
are posting questions about is that you need to define what you mean
by a week.  Is it the ISO standard?  Is the week of the year?  Is it
the first day of the week (Sunday or Monday according your regional
settings)?

I usually group by the first day of the week for reporting purposes,
unless required to do otherwise.

SELECT DATEADD(wk, DATEDIFF(wk,0, CURRENT_TIMESTAMP), 0),
DATEADD(wk, -1, DATEADD(wk, DATEDIFF(wk,0, CURRENT_TIMESTAMP), 0)),
DATEADD(wk, -2, DATEADD(wk, DATEDIFF(wk,0, CURRENT_TIMESTAMP), 0)),
DATEADD(wk, -3, DATEADD(wk, DATEDIFF(wk,0, CURRENT_TIMESTAMP), 0))

HTH,
Stu

03-Feb-10 10:52 AM

sorry for the confusion,

given a date, does this date occur in the current week, last week, 2 weeks
ago, etc.

03-Feb-10 11:12 AM

Maybe this:

DECLARE @dt DATETIME;

SET @dt = '20100120';

SELECT DATEPART(WEEK, CURRENT_TIMESTAMP) AS current_week,
DATEPART(WEEK, @dt) AS date_week,
DATEDIFF(WEEK, @dt, CURRENT_TIMESTAMP) diff_in_weeks;

--
Plamen Ratchev
http://www.SQLStudio.com

03-Feb-10 12:09 PM

thanks all for the help,
rod.