Prev: SQL - Matchcode with multiple records
Next: Split variable and exec stored procedure
From: rodchar on 3 Feb 2010 09:31
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 3 Feb 2010 09:51
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 3 Feb 2010 09:59
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 3 Feb 2010 09:59
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 3 Feb 2010 10:00
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
 |  Next  |  Last
Pages: 1 2
Prev: SQL - Matchcode with multiple records
Next: Split variable and exec stored procedure