How do I calculate the week number given a date?

Question

How do I calculate the week number given a date?

Vaghasiya Nilesh

SSChasing Mays

Points: 633
More actions
July 17, 2010 at 1:40 am

#224864

hi guys
How do I calculate the week number given a date for current year?

Viewing 15 posts - 1 through 15 (of 18 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

There are several concepts depending on what type week number you're looking for.

If you need the week numbered as per the restrictions described in BOL (BooksOnLine, the SQL Server help system usually installed together with SQL server), section "dateparts [SQL Server], DATEPART" then DATEPART is one option.

If you're looking for the ISO week number, then you might want to have a look at BOL, section "create function, Example A". But please be aware that the function is designed as a scalar-valued function that might have performance issues. You should modify it to an inline table-valued function.

Another option would be to build a calendar table. Please search this site for related scripts. If you need further assistance please post back what you've tried so far and where you get stuck.

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]

Eralper SSCarpal Tunnel Points: 4438 More actions · Answer 2

Hello,

Easiest solution is using DATEPART function

select DATEPART(WEEK,GETDATE())

If that is not useful for your requirements, you can also think of to create date table in SQL Server

Eralper
SQL Server and T-SQL Tutorials and Articles
Microsoft Certification and Certification Exams

funooni Ten Centuries Points: 1314 More actions · Answer 3

Going further from Eralper's idea;

You can use

select DATEPART(WEEK,DAY(getdate()))

to get the week of the month.

GETDATE() can be replaced by any datetime variable or even a string like '2010-12-07' ; i am using 'yyyy-dd-mm' format.

Paul White SSC Guru Points: 150467 More actions · Answer 4

funooni (7/19/2010)
Going further from Eralper's idea;

To be fair, I think Lutz covered it first 🙂

You can use select DATEPART(WEEK,DAY(getdate())) to get the week of the month. GETDATE() can be replaced by any datetime variable or even a string like '2010-12-07' ; i am using 'yyyy-dd-mm' format.

This does not work. The DAY built-in function returns just the day date part of the supplied date, so for 19th July 2010, it would return 19.

Supplying the value 19 to the DATEPART function results in 19 being implicitly converted to a date (19 days after the base date '1900-01-01') to give '1900-01-20'. Knowing the week number of 20th January 1900 is unlikely to be the desired result 😉

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

funooni Ten Centuries Points: 1314 More actions · Answer 5

All marks go to Lutz 😉

This does not work.

Well i just checked it and it does work.

The DAY built-in function returns just the day date part of the supplied date, so for 19th July 2010, it would return 19.

Yes this would return 19

Supplying the value 19 to the DATEPART function results in 19 being implicitly converted to a date (19 days after the base date '1900-01-01') to give '1900-01-20'. Knowing the week number of 20th January 1900 is unlikely to be the desired result

Supplying the value value 19 to DATEPART function with the first option 'WEEK', it will return 3 which means this is the 3rd week of the month.

One more thing to add the function DATEPART(WEEK,<ANY NUMBER FROM 0-364>) will return the current week number.

For instance,

select DATEPART(WEEK,21)

will return 4 as 3 weeks have finished.

Let me know please if this does not work and correct me if i am wrong at perceiving this.

Thanks

Paul White SSC Guru Points: 150467 More actions · Answer 6

funooni (7/19/2010)
Well i just checked it and it does work. Supplying the value value 19 to DATEPART function with the first option 'WEEK', it will return 3 which means this is the 3rd week of the month.

We're after the week number of the year here, not the week number of the month 🙂

DECLARE @dt DATETIME;

SET @dt = '2010-07-19';

SELECT DATEPART(WEEK, DAY(@dt)); -- 3

SELECT DATEPART(WEEK, @dt); -- 30

The second example shows that 19th July 2010 is in week 30 of this year (with my current system settings).

It's unclear to me what your code is showing. 19th July isn't in the third week of July on my calendar (July started on a Thursday, and weeks run from Monday for me).

It is in the third week of July if you consider the first 7 days of any month to be the 'first week' and so on - is that the purpose of your function? If so, why does it return 4 for 20th July 2010? If I needed to know this sort of information, I would probably just use something like SELECT DAY(@dt) / 7 + 1; there's no need for the DATEPART at all.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 7

funooni (7/19/2010)
...
Supplying the value value 19 to DATEPART function with the first option 'WEEK', it will return 3 which means this is the 3rd week of the month.
One more thing to add the function DATEPART(WEEK,<ANY NUMBER FROM 0-364>) will return the current week number.
For instance,
select DATEPART(WEEK,21)
will return 4 as 3 weeks have finished.
Let me know please if this does not work and correct me if i am wrong at perceiving this.
Thanks

You are wrong at perceiving how DATEPART function works!

And it works exactly as explained by Paul White NZ.

Check the BoL.

The second input parameter of DATEPART function is of DATETIME datatype.

When you supply the integer there, it is implicitly converted into datetime as per following:

1 Jan 1900 + INT value.

You can supply positive or negative integer values as you wish...

The number of week will properly match only for years which week days match ones of year 1900! Last such year was 2007 and the next one will be 2018!

So if you run

select DATEPART(WEEK,364)

it will return 53 always, regardless when (which year) you run it as it represents 31 Dec 1900!

But correct week number for 31 Dec 2000 would be 54 - leap year man!

select DATEPART(WEEK,'31 Dec 2000')

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

LutzM SSC Guru Points: 107049 More actions · Answer 8

Like I stated in my first post: It depends on the requirement.

You cannot use DATEPART() if you need to get the week as per ISO specification.

Furthermore, DATEPART(WEEK,somedate) will return different results based on the setting of DATEFIRST.

Straight from BOL:

The week (wk, ww) datepart reflects changes made to SET DATEFIRST. January 1 of any year defines the starting number for the week datepart, for example: DATEPART(wk, 'Jan 1, xxxx') = 1, where xxxx is any year.

Here are a few examples. Please note, that none of the DATEPART() function does return the correct ISO week and neither are the results consistent.

DECLARE @date DATETIME

SET @date='20100102'--Saturday, January 1st, as per ISO definition week 53 of year 2009

SET DATEFIRST 1