(sql server 2005) How to determine the WEEK number starting from GETDATE()

Question

Possible Duplicate:
Getting week number off a date in MS SQL Server 2005?

Please suppose (i am in Italy) that all the weeks start with Monday and end with Sunday.

I would like to write a scalar valued function that determines the number of the week with reference to GETDATE()

Thank you in advance for your kind cooperation.

PLEASE PAY ATTENTION TO THIS: The SELECT has to be INDIPENDENT from the SET DATEFIRST command!!

Do you want the number of weeks of a given timespan(f.e. `dbo.getNumWeeks(GetDate(), otherDate)`)? — Tim Schmelter, Jul 27 '12 at 16:22
No, sorry, I try to explain myself. Last Monday (July 23rd, 2012) was the beginning of the 30th week, and the next Sunday (July 29th, 2012) will be the end of the 30th week. This is in Italy. In other countries, such as in America, the weeks start from SUNDAY and end with SATURDAY. — UltraCommit, Jul 27 '12 at 16:29
I need an unique select statement, indipendent from "set datefirst" command. — UltraCommit, Jul 27 '12 at 16:31
Try `datepart(iso_week, getdate())` It will change the weeknumber on Mondays. — Mikael Eriksson, Jul 27 '12 at 16:31
`iso_week` will work for you from SQL Server 2008. [DATEPART](http://msdn.microsoft.com/en-us/library/ms174420(v=sql.100).aspx). — Mikael Eriksson, Jul 27 '12 at 16:45
This was the answer I was finding for: http://stackoverflow.com/questions/348880/getting-week-number-off-a-date-in-ms-sql-server-2005 — UltraCommit, Jul 27 '12 at 16:49
Great! I'm glad that other answer also has a detailed explanation of the problems inherent in year-week numbers as well. — Ted Spence, Jul 27 '12 at 17:01

score 3 · Answer 1 · edited May 23 '17 at 11:48

3

Can you use the two liner?

  SET DATEFIRST 1;
  SELECT DATEPART( WEEK , GETDATE())

for today

SELECT DATEPART( WEEK , GETDATE())

returns 30 whereas

SET DATEFIRST 1;
SELECT DATEPART( WEEK , GETDATE())

returns 31

Edit Untested but based on This Question you can get the equivalent of the SQL Server 2008 datepart(iso_week, getdate()) (which i believe is what you want) with the following select statement

SELECT ISOWeek = (DATEPART(DY, Th) - 1) / 7 + 1
FROM (SELECT Th = DATEADD(D, 3 - (DATEPART(DW, getdate()) + @@DATEFIRST - 2) % 7, getdate())) s

edited May 23 '17 at 11:48

Community

1
1

answered Jul 27 '12 at 16:18

Manatherin

4,169
5
36
52

1

I am so sorry, I need a unique select statement... – UltraCommit Jul 27 '12 at 16:30

SPFiredrake · Accepted Answer · 2012-07-27T17:07:44.023

Here's a little snippet that should do what you need.

DECLARE @firstOfYear DATETIME
SET @firstOfYear = STR(Year(GETDATE()), 4)+'-01-01 00:00:00'
SELECT DATEDIFF(ww, @firstOfYear - ((DATEPART(dw, @firstOfYear) + 5) % 7), GETDATE())

Keep in mind that if you want to set the week start to on a different day, just change the +5 to the value based on 7 - dw. This is for MSSQL.

This works by getting the first day of the year and finding the day of the starting week on or before that day. Then we get the number of weeks between whatever date was passed in and that "first" week start. If you want to allow any date to be passed in, just replace all GETDATE calls with your parameter and you should be good to go. If you need a single select statement:

SELECT 
    DATEDIFF(ww, day1 - ((DATEPART(dw, day1) +5) % 7), GETDATE()) 
FROM 
    (SELECT CAST(STR(Year(GETDATE()), 4)+'-01-01 00:00:00' AS DATETIME) day1) d

Thank you very much for your single select statement. – UltraCommit Jul 27 '12 at 19:53 — UltraCommit, Jul 27 '12 at 19:53

score 0 · Answer 3 · answered Jul 27 '12 at 16:18

0

select datepart(week,getdate())

more at http://msdn.microsoft.com/en-us/library/aa258265(v=sql.80).aspx

answered Jul 27 '12 at 16:18

Royi Namir

144,742
138
468
792

select datepart(weeknum,getdate()) does not work, gives error – UltraCommit Jul 27 '12 at 16:20
1

Does this account for the stipulation of Monday being the start of the week? – Daniel DiPaolo Jul 27 '12 at 16:22
The select has to be indipendent from the SET DATEFIRST command, could you help me for this? I've just edited my question. – UltraCommit Jul 27 '12 at 16:22

score 0 · Answer 4 · answered Jul 27 '12 at 16:27

0

declare @oldDF int
set @oldDF = @@DATEFIRST

set DATEFIRST 1
select DATEPART(WEEK, GETDATE())

set DATEFIRST @oldDF

answered Jul 27 '12 at 16:27

Dan Pichelman

2,312
2
31
42

I need an unique select statement, how can I achieve this? – UltraCommit Jul 27 '12 at 16:31

(sql server 2005) How to determine the WEEK number starting from GETDATE()

4 Answers4