Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

How do I calculate the week number given a date? Expand / Collapse
Author
Message
Posted Saturday, July 17, 2010 1:40 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 17, 2010 2:06 AM
Points: 23, Visits: 35
hi guys
How do I calculate the week number given a date for current year?
Post #954315
Posted Saturday, July 17, 2010 2:17 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Sunday, September 7, 2014 11:27 PM
Points: 7,164, Visits: 13,257
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
Post #954324
Posted Monday, July 19, 2010 2:15 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 24, 2013 1:55 AM
Points: 178, Visits: 465
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
Post #954646
Posted Monday, July 19, 2010 2:42 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 1:18 AM
Points: 205, Visits: 230
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.
Post #954649
Posted Monday, July 19, 2010 3:30 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:48 PM
Points: 11,194, Visits: 11,169
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #954668
Posted Monday, July 19, 2010 7:01 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 1:18 AM
Points: 205, Visits: 230

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
Post #954786
Posted Monday, July 19, 2010 7:24 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:48 PM
Points: 11,194, Visits: 11,169
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #954802
Posted Monday, July 19, 2010 7:28 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 5:50 AM
Points: 2,856, Visits: 5,124
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #954807
Posted Monday, July 19, 2010 10:16 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Sunday, September 7, 2014 11:27 PM
Points: 7,164, Visits: 13,257
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
Post #954934
Posted Monday, July 19, 2010 4:05 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 1:18 AM
Points: 205, Visits: 230
Thanks to all.

Concepts cleared.
Post #955172
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse