From: rodchar on
Hi All,

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

Thanks,
rodchar
From: Uri Dimant on
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]



"rodchar" <rodchar(a)discussions.microsoft.com> wrote in message
news:5A35E44C-4538-4EEF-AF1F-D51C594ABB51(a)microsoft.com...
> Hi All,
>
> Given a date could I determine what week I'm in starting with the current
> week, then week1, week2, week3
>
> Thanks,
> rodchar


From: rodchar on
currentweek, week1ago, week2ago, week3ago

"rodchar" wrote:

> Hi All,
>
> Given a date could I determine what week I'm in starting with the current
> week, then week1, week2, week3
>
> Thanks,
> rodchar
From: Russell Fields on
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








"rodchar" <rodchar(a)discussions.microsoft.com> wrote in message
news:5A35E44C-4538-4EEF-AF1F-D51C594ABB51(a)microsoft.com...
> Hi All,
>
> Given a date could I determine what week I'm in starting with the current
> week, then week1, week2, week3
>
> Thanks,
> rodchar


From: Plamen Ratchev on
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