CODEWITHSUNDEEP
sql-servert-sql
FrenkyB
FrenkyB

Reputation: 7217

Get week number in year from date

I am looking for a query which will return the week number from given date.

What I've already tried is this:

select datepart(wk, '2017-02-01')

but this returns 5 instead of 6. (february 1st is the 6th week of the year).

(week numbers with red)

enter image description here

Upvotes: 55

Views: 240638

Answers (5)

barbara.post
barbara.post

Reputation: 1671

For week starting on Monday, without using "set datefirst":

select datepart(iso_week, '2023-03-22')

Because in a SQL view you cannot set datefirst which is a session variable. I found the right documentation:

ISO Week Number You can also return the ISO week number from a date by using the iso_week argument. ISO weeks start on Mondays and the first week of a year contains January 4 of that year.

Upvotes: 2

MoNajar
MoNajar

Reputation: 118

I know this is very old, but it might help somebody. If you mean you want the exact number of week in some given date, then you might use this query:

SELECT WEEK("2022-05-29"); --> 22

resource: https://www.w3schools.com/sql/func_mysql_week.asp

Upvotes: -1

TheOx
TheOx

Reputation: 122

You can get the week number from a specific column which contains a date by using:

DATEPART(WK, orders.createddate) AS Week,

This is taken from the DATEPART functions available below:

   DATEPART(YY, orders.createddate) AS Year, 
   DATEPART(QQ, orders.createddate) AS Quarter, 
   DATEPART(WK, orders.createddate) AS Week, 
   DATEPART(DY, orders.createddate) AS dayofYear, 
   DATEPART(MM, orders.createddate) AS Month, 
   DATEPART(DD, orders.createddate) AS Date, 
   DATEPART(hour, orders.createddate) AS Hour, 
   DATEPART(minute, orders.createddate) AS Minute, 
   DATEPART(second, orders.createddate) AS Second, 
   DATEPART(millisecond, orders.createddate) AS Millsecond, 
   DATEPART(microsecond, orders.createddate) AS Microsecond, 
   DATEPART(nanosecond, orders.createddate) AS Nanosecond,

Upvotes: 8

czeinerb
czeinerb

Reputation: 379

You can also consider using 'iso_week' instead of 'week' parameter in 'datepart'. This case you can avoid using 'set datefirst 1', which can be convenient if you can only use a single select.

More details here about 'iso_week': "ISO 8601 includes the ISO week-date system, a numbering system for weeks. Each week is associated with the year in which Thursday occur"

You can compare the two like this:

SELECT datepart(ISO_WEEK, '2020.01.01') -- Wed
SELECT datepart(WEEK, '2020.01.01') -- Wed
SELECT datepart(ISO_WEEK, '2020.01.05') -- Sun
SELECT datepart(WEEK, '2020.01.05') -- Sun
SELECT datepart(ISO_WEEK, '2020.01.06') -- Mon
SELECT datepart(WEEK, '2020.01.06') -- Mon

Note the difference for Sunday, 5 Jan 2020:

-----------
1
1
1
2
2
2

Upvotes: 26

SqlZim
SqlZim

Reputation: 38063

You probably need to specify the first day of your week with set datefirst:

set datefirst 1;
select datepart(week, '2017-02-01');

returns 6

Depending on the default language, your datefirst might be set to 7. 

set datefirst 7;
select datepart(week, '2017-02-01');

returns 5

rextester demo: http://rextester.com/KEPB52852

Upvotes: 76

Related Questions