3

I'm calculating the week for a specific date in SQL for example '2016-01-20' (yyyy-mm-dd) but SQL returns week: 4, and that is wrong because this year the first week started on '2016-01-04' the result must be week: 3.

I think the issue is generatad because 2015 was a year with 53 weeks, any solution to that? Thank you and I'm sorry for my bad English

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Sebastian Pelaez
  • 126
  • 2
  • 12
  • 1
    To clarify your question, please list the following: 1) The Database you are using the datepart function (SQL Server or MS Access); 2) The specific query you are writing; 3) The results or error you are seeing; 4) The results you are expecting to see. –  Jan 20 '16 at 17:16
  • If this is SQL Server then `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.` -msdn – SQLChao Jan 20 '16 at 17:20
  • 3
    What RDBMS are you using: SQL Server, MySQL, Oracle ... ? It makes a difference to the answer. – Esoteric Screen Name Jan 20 '16 at 17:21
  • 2
    Assuming SQL Server - perhaps you are looking for the datepart option `ISO_WEEK`? [Here is some light reading](http://dba.stackexchange.com/questions/38680/iso-week-vs-sql-server-week) – Bridge Jan 20 '16 at 17:21
  • Calculating "week in year" is highly (if not incredibly) subjective. If your business requirements do not match those of your RDBMS, you will have to "roll your own" logic. – Philip Kelley Jan 20 '16 at 17:35
  • 1
    According to your question, weeks start on Mondays. If that's your business situation you may want to create a calendar table that includes this sort of thing. We have one in our data warehouse and the field is called week_beginning. – Dan Bracuk Jan 20 '16 at 17:38
  • Based on the question the OP definition of a week is based on ISO. – dnoeth Jan 20 '16 at 17:40

2 Answers2

0

In tSQL the DATEPART() is returning the correct data based on US and Most of Europe as well as UK See here

You can use SET DATEFIRST to adjust the start position however.

Fuzzy
  • 3,810
  • 2
  • 15
  • 33
  • 1
    The OP needs to use `ISO_WEEK`. And BOL are plain wrong, ISO weeks are common across Europe, not only *Norway and Sweden* (and other parts of the world, too) – dnoeth Jan 20 '16 at 17:34
0

The ISO 8601 definition for week 01 is the week with the year's first Thursday in it. I am using Intersystems cache which apparently does not account for that either. So I have used this to address that

CASE WHEN 7-datepart(dw,dateadd(dd,1,dateadd(yy,datediff(yy,0,getdate())-1,0))) < 2 
THEN datepart(wk,getdate())-1 ELSE datepart(wk,getdate()) END as WeekNum
CoSpringsGuy
  • 1,615
  • 1
  • 13
  • 16