Date to week function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Date to week function

Author

Topic

wotrac
Yak Posting Veteran

98 Posts

Posted - 2005-11-10 : 15:16:06

Can anyone help me with the following?

I need to be able to pass a date to a function and return a week number, based on a fixed year start date. The year start date will change every year.

Kristen
Test

22859 Posts

Posted - 2005-11-10 : 15:21:58

SQL Server can give you the WEEK component of a DATETIME value. You can also get the WEEK component of your Base Date, and I suspect you could use those two to get a manipulated WeekNo, base the WEEK of Your-Start_date, to suit your needs.

Kristen

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2005-11-10 : 15:32:32

declare @a as datetime
set @a = '01-22-2005'

select
@a as StartDate,
DatePart(Week, @a) as StartWeek,
DatePart(Week, GetDate()) as CurrentWeek,
DatePart(Week, GetDate())-DatePart(Week, @a) as AdjustedWeek

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-11-10 : 15:32:58

You can use SQL Server's built-in DATEPART function.

SELECT DATEPART(wk, GETDATE())
SELECT DATEPART(wk, '01/01/2000')

Tara Kizer
aka tduggan

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-11-10 : 23:09:57

the code is not full prove yet but it should give u an idea


declare
	@week_1_date	datetime,
	@user_date	datetime

select	@week_1_date	= '2005-03-14'
select	@user_date	= '2005-02-20'

select	week_no = case when datepart(week, @user_date) >= datepart(week, @week_1_date) then
				datepart(week, @user_date) - datepart(week, @week_1_date) + 1
			else
				datepart(week, @user_date) + 
				(datepart(week, convert(char(4), year(@week_1_date)) + '1231')) -  
				datepart(week, @week_1_date) + 1
			end

[KH]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources