DatePart(Week, @Date) Returns wrong weeknumber?

Question

Hi,
I notice the following results of query:

Declare			@Date1				DateTime
	,			@Date2				DateTime

Set				@Date1			=	'1/1/2010'
Set				@Date2			=	'1/4/2010'
Select			@Date1			As	Jan_1_2010
	,			DatePart(Week,	@Date1)	WeekJan_1_2010
	,			@Date2			As	Jan_4_2010
	,			DatePart(Week,	@Date2)	WeekJan_4_2010
	,			@@DateFirst		As	DateFirst

Jan_1_2010              WeekJan_1_2010 Jan_4_2010              WeekJan_4_2010 DateFirst
----------------------- -------------- ----------------------- -------------- ---------
2010-01-01 00:00:00.000              1 2010-01-04 00:00:00.000              2         7

(1 row(s) affected)

The weeknumber of 1/1/2010 should be 53 instead of 1
The weeknumber of 1/4/2010 should be 1 instead of 2
What can I do to obtain the correct result?
Thanks in advance, Jos

Answer 1

In SQL Server 2008 there is support for ISO WEEK. Demo follows.

-- SQL Server 2008 T-SQL code - ISO WEEK
SELECT ISO_WEEK = DATEPART(isowk,'2010-01-01')
-- 53

Pre SQL 2008, you need to calculate it.

Link: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=60510

---

Kalman Toth
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016

Answer 2

The weeknumber of 1/1/2010 should be 53 ?

HOW? :O

Answer 3

Dear rajpes,

If I look at the calendar in front of me, it tells me 1 januari 2010 (I am Dutch so I have a Dutch calendar) is week 53, and 4 janauari 2010 is week 1 etc.

Regards, Jos

Answer 4

Thanks, SqlUsa
Regards, Jos

Answer 5

Dear all,

I'm Dutch too and we always strugle with our week numbers. We use what is called the Gregorian Calendar which means our week 1 is the first week of the year that has at least 4 days in January. This can result in a year having 53 weeks and January 1st not falling in week 1! To be complete Dutch weeks start at Monday.

Ragards, Raymond

Answer 6

You can read more at http://en.wikipedia.org/wiki/ISO_8601. There are a lot of translations as well.

Answer 7

Welcome to the Microsoft code museum :)  Microsoft invented their own week numbering and ignored the ISO standards.  We have a correction in T-SQL 2012  -- DATEPART(isowk,'2010-01-01') = 53.  We also have DATE data type so you can stop with the old DATETIME disaster.  And there is no excuse for not using the ISO-8601 "yyyy-mm-dd" instead of dialect. 

You are also supposed to be writing this as "yyyy-Www-d" where yyyy is the year, ww is the week number with leading zeroes and d is 1 (Monday) to 7(Sunday) for the day of the week. Loafd this into your Calendar table. 

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Answer 8

Hi Kalman,

Do you think that the output of DATEPART(isowk,'2010-01-01') should depend on the value of

@@DATEFIRST?

---

Remember to mark as an answer if this post has helped you.

Answer 9

Hi Igor,

ISO week starts on Monday.

Wiki article quote:"The ISO week date system is a leap week calendar system that is part of the ISO 8601 date and time standard. The system is used (mainly) in government and business for fiscal years, as well as in timekeeping.

The system uses the same cycle of 7 weekdays as the Gregorian calendar. Weeks start with Monday. ISO week-numbering years have a year numbering which is approximately the same as the Gregorian years, but not exactly (see below). An ISO week-numbering year has 52 or 53 full weeks (364 or 371 days). The extra week is here called a leap week (ISO 8601 does not use the term)."

http://en.wikipedia.org/wiki/ISO_week_date

I think we should have 4 weeks x 13 months calendar. The last week of the year would have  1 or 2 extra days: EarthDay & StarDay. Name of the 13th month Undicember. Each year, each month, each week would start on Monday.

---

Kalman Toth
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016

Answer 10

Thank you for help.

---

Remember to mark as an answer if this post has helped you.