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" 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" 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