SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How do I calculate the week number given a date?


How do I calculate the week number given a date?

Author
Message
Vaghasiya Nilesh
Vaghasiya Nilesh
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

Group: General Forum Members
Points: 85 Visits: 35
hi guys
How do I calculate the week number given a date for current year?
LutzM
LutzM
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24515 Visits: 13559
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
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Eralper
Eralper
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1004 Visits: 466
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
funooni
Old Hand
Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)

Group: General Forum Members
Points: 397 Visits: 294
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
Paul White
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36690 Visits: 11361
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
SQLblog.com
@SQL_Kiwi
funooni
funooni
Old Hand
Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)

Group: General Forum Members
Points: 397 Visits: 294
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>Wink 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
Paul White
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36690 Visits: 11361
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
SQLblog.com
@SQL_Kiwi
Eugene Elutin
Eugene Elutin
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12978 Visits: 5478
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>Wink 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
LutzM
LutzM
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24515 Visits: 13559
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
SELECT @@DATEFIRST AS DATEFIRST,DATEPART(WEEK,@date) AS WeekNo
SET DATEFIRST 2
SELECT @@DATEFIRST AS DATEFIRST,DATEPART(WEEK,@date) AS WeekNo
SET DATEFIRST 3
SELECT @@DATEFIRST AS DATEFIRST,DATEPART(WEEK,@date) AS WeekNo
SET DATEFIRST 4
SELECT @@DATEFIRST AS DATEFIRST,DATEPART(WEEK,@date) AS WeekNo
SET DATEFIRST 5
SELECT @@DATEFIRST AS DATEFIRST,DATEPART(WEEK,@date) AS WeekNo
SET DATEFIRST 6
SELECT @@DATEFIRST AS DATEFIRST,DATEPART(WEEK,@date) AS WeekNo
SET DATEFIRST 7
SELECT @@DATEFIRST AS DATEFIRST,DATEPART(WEEK,@date) AS WeekNo





Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
funooni
funooni
Old Hand
Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)

Group: General Forum Members
Points: 397 Visits: 294
Thanks to all.

Concepts cleared.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search