Week of 1st day of Year

Question

Week of 1st day of Year

GPhilippe

SSC Rookie

Points: 37
More actions
December 10, 2004 at 8:07 am

#102308

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,

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

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

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 More actions · Answer 2

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 More actions · Answer 3

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
Thank you

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 4

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
Thank 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.

Change is inevitable... Change for the better is not.

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

omelo Grasshopper Points: 24 More actions · Answer 5

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
Thank 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: 1004343 More actions · Answer 6

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.

Change is inevitable... Change for the better is not.

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

omelo Grasshopper Points: 24 More actions · Answer 7

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: 1004343 More actions · Answer 8

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.

Change is inevitable... Change for the better is not.

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

omelo Grasshopper Points: 24 More actions · Answer 9

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