Prev: SQL - Matchcode with multiple records
Next: Split variable and exec stored procedure
From: Stuart Ainsworth on 3 Feb 2010 10:18
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
From: rodchar on 3 Feb 2010 10:52
sorry for the confusion,

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

"Plamen Ratchev" wrote:

> 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
> .
>
From: Plamen Ratchev on 3 Feb 2010 11:12
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
From: rodchar on 3 Feb 2010 12:09
thanks all for the help,
rod.

"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
First  |  Prev  | 
Pages: 1 2
Prev: SQL - Matchcode with multiple records
Next: Split variable and exec stored procedure