Week Number of a specific Date

Question

Post reply

Week Number of a specific Date

Vijay Sinha

Mr or Mrs. 500

Points: 591
More actions
May 24, 2010 at 1:16 am

#139157

Can anybody tell me to How to get the week number of a specific Date in SQL Server.
Like : say Date "05/03/2010" belongs to the 1st week of May year 2010,
So, the same way I need to know the week number of a given (variable) date.
I need to write / use a Function in SQL server which will return the Number (INT) (i.e. 1, 2 etc...) against a specific (Parameter) date.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 1

Seems like a job for a calendar table

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

Clear Sky SQL
My Blog[/url]

Gopi Muluka SSCarpal Tunnel Points: 4594 More actions · Answer 2

See this

DECLARE @dt DATETIME, @WeekNumber INT

SET @dt='03/01/2010'

SELECT @WeekNumber=DATEPART(wk,@DT)

SELECT @WeekNumber

LutzM SSC Guru Points: 107049 More actions · Answer 3

Gopi Muluka (5/24/2010)
See this
DECLARE @dt DATETIME, @WeekNumber INT
SET @dt='03/01/2010'
SELECT @WeekNumber=DATEPART(wk,@DT)
SELECT @WeekNumber

You need to be careful with DATEPART(wk,...) since it depends on the setting of DATEFIRST. Furthermore, it always starts with week#1 for January 1st, which is not always true for ISO weeks.

Like Dave recommended: use a calendar table or you'd have to write your own getWeekFromDate function. Side note: the one shown in BOL (BooksOnLine) will give the correct result but you should modify it to be used as a iTVF (inline table-valued function). Otherwise this function may cause performance issues.

Edit: typo fixed

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Danny Sheridan SSChasing Mays Points: 642 More actions · Answer 4

Vijay,

I would take a look at this workbench off of SimpleTalk. It explains everything you are looking for and then some in depth, and also shows multiple ways of achieving the same results.

Using Dates and Times in SQL Server (By: Robyn Page)

http://www.simple-talk.com/sql/learn-sql-server/robyn-pages-sql-server-datetime-workbench/

Edit - This is a DATE-TIME function workbench. Demonstrates many uses of most of the DATE-TIME commands.

Danny Sheridan
Comtekh, Inc.