Week of 1st day of Year

  • GPhilippe

    Newbie

    Points: 7

    Hello,

    I'm in trouble when execute Datepart(wk,'01/01/2005') it return 1 .But in france the 1/1/05 is in 53 week of 2004 because the rule is  :

    the first week need 4 days.

    Is there any parameter in SQL ?

    Thanks,

  • Frank Kalis

    SSC Guru

    Points: 111183

    Before reinventing the wheel have a look in BOL at "CREATE FUNCTION". You'll find a UDF to calculate ISO weeks.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • Shaun McKee

    SSC Veteran

    Points: 216

    declare @FirstofYear as datetime

    set @FirstofYear = '01/01/2005'

    select Case

    when DATEPART(dw, @FirstofYear) <= 4 then Datepart(wk, @FirstofYear)

    else Datepart(wk, dateadd(day, -1, @FirstofYear))

    end

    This assumes that Sunday is the 7th day of the week. (BOL: "The U.S. English default is 7, Sunday")  If you need to adjust the days of the week value use @@DATEFIRST

  • omelo

    Grasshopper

    Points: 24

    Hi, This post is old but it is related to the question I have.
    I'm using  this syntax" datepart(wk,transDate) as Week" on a report , I'm getting the results I want, except for the last week of Dec 2018, first on 2019
    Week 1 starts on Sunday Dec 30 ending on January 5th 2019 .Datepart returns Dec 30 and Dec 31 on week 53, It should be grouped on week 1.
    I  tried week, iso_week but I'm not getting the results I need. 
    Can some one help me resolving this issue?
    note: I'm using sql 2012
    T
    hank you

  • Jeff Moden

    SSC Guru

    Points: 994284

    omelo - Saturday, March 2, 2019 9:10 AM

    Hi, This post is old but it is related to the question I have.
    I'm using  this syntax" datepart(wk,transDate) as Week" on a report , I'm getting the results I want, except for the last week of Dec 2018, first on 2019
    Week 1 starts on Sunday Dec 30 ending on January 5th 2019 .Datepart returns Dec 30 and Dec 31 on week 53, It should be grouped on week 1.
    I  tried week, iso_week but I'm not getting the results I need. 
    Can some one help me resolving this issue?
    note: I'm using sql 2012
    T
    hank you

    What is the first day of the week for you?  It looks like you're saying Sunday is the first day of the week but I want to be sure.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • omelo

    Grasshopper

    Points: 24

    Jeff Moden - Monday, March 4, 2019 5:58 AM

    omelo - Saturday, March 2, 2019 9:10 AM

    Hi, This post is old but it is related to the question I have.
    I'm using  this syntax" datepart(wk,transDate) as Week" on a report , I'm getting the results I want, except for the last week of Dec 2018, first on 2019
    Week 1 starts on Sunday Dec 30 ending on January 5th 2019 .Datepart returns Dec 30 and Dec 31 on week 53, It should be grouped on week 1.
    I  tried week, iso_week but I'm not getting the results I need. 
    Can some one help me resolving this issue?
    note: I'm using sql 2012
    T
    hank you

    What is the first day of the week for you?  It looks like you're saying Sunday is the first day of the week but I want to be sure.

    Yes, it is sunday.so Dec 30 and Dec 31 should be on Week 1
    Dec 30 to Jan 5= Week 1
    hank youT

  • Jeff Moden

    SSC Guru

    Points: 994284

    The following formula will do it for you.

    (DATEPART(dy,DATEADD(dd,-53684,DATEDIFF(dd,-53684,@SomeDate)/7*7+6))-1)/7+1

    1.  It finds the first Sunday on or previous to the given date (-53684 is the date serial number for Sunday, 07 Jan 1753).
    2.  It adds 6 days to that to find the Saturday following the given date.
    3.  If figures out  what day of the year is for that Saturday and subtracts one from that to make it "zero based".
    4.  It divides that by 7 to come up with a zero based week number.
    5.  It adds one to that to return the one based week number.

    The code will not work (silent failure to calculate the correct number) for any dates prior to 07 Jan 1753 nor for any dates in the final week of the year 9999.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • omelo

    Grasshopper

    Points: 24

    Jeff Moden - Monday, March 4, 2019 10:37 AM

    The following formula will do it for you.

    (DATEPART(dy,DATEADD(dd,-53684,DATEDIFF(dd,-53684,@SomeDate)/7*7+6))-1)/7+1

    Thank you for answering, this works

  • Jeff Moden

    SSC Guru

    Points: 994284

    omelo - Monday, March 4, 2019 10:44 AM

    Jeff Moden - Monday, March 4, 2019 10:37 AM

    The following formula will do it for you.

    (DATEPART(dy,DATEADD(dd,-53684,DATEDIFF(dd,-53684,@SomeDate)/7*7+6))-1)/7+1

    Thank you for answering, this works

    I just added an explanation for why it works.  Since you have to support the code, you might as know how it works.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • omelo

    Grasshopper

    Points: 24

    Jeff Moden - Monday, March 4, 2019 10:47 AM

    omelo - Monday, March 4, 2019 10:44 AM

    Jeff Moden - Monday, March 4, 2019 10:37 AM

    The following formula will do it for you.

    (DATEPART(dy,DATEADD(dd,-53684,DATEDIFF(dd,-53684,@SomeDate)/7*7+6))-1)/7+1

    Thank you for answering, this works

    I just added an explanation for why it works.  Since you have to support the code, you might as know how it works.

    Very clever! Thanks again

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply