Last Friday of given week

Question

Last Friday of given week

Theo Jacobs

Say Hey Kid

Points: 694
More actions
July 19, 2007 at 10:29 am

#117387

Hi ppl,
I'm looking to get back the date (dmy) of the last friday of a given week to be passed as yyyyww into a function.
The first day of the week will always be Monday for this.
I really need something that works as I have spent way over my budget in getting this correct. The problem I am currently struggling with is because of leap years the date gets screwy.
ANY HELP PLEASE.
Thanx

Viewing 15 posts - 1 through 14 (of 14 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Please post sample data, required results from that data, the solution you currently have and what is wrong with it. That'll get you an answer much faster than you can imagine :-).

Daryl AZ Hall of Fame Points: 3087 More actions · Answer 2

The code below should do the trick.. TSQL below not the actual function...

DECLARE @argYearWeek VARCHAR(6), @aWeek INT, @aJan1 DATETIME, @aFridayOffset int

SET @argYearWeek = '200733'

SET @aWeek = CAST(RIGHT(@argYearWeek,2) AS INT)

SET @aJan1 = LEFT(@argYearWeek, 4) + '/01/01'

SELECT @aFridayOffset = 6 - DATEPART(dw,@ajan1)

--Note: this is dependent upon [SET DATEFIRST]

SELECT DATEADD(dd, (@aWeek - 1) * 7 + @aFridayOffset, LEFT(@argYearWeek, 4) + '/01/01')

daralick

Daryl AZ Hall of Fame Points: 3087 More actions · Answer 3

hmm. set all the variables so nicely above and then didn't replace

....

SELECT DATEADD(dd, (@aWeek - 1) * 7 + @aFridayOffset, @aJan1)

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 4

The last Friday of a given week? Do you have weeks with more then one Friday?

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 5

Is week 1 of any year always starting with the monday on January 1 or nearest monday after?

Or do you want simple ISO week calulation? Then remember that there can be two week 1 or two week 52 any a year.

N 56°04'39.16"
E 12°55'05.25"

Sergiy SSC Guru Points: 110208 More actions · Answer 6

This does not depend on DATEFIRST settings:

Declare @D datetime

SET @D = GETDATE()+1

SELECT @D, DATEADD(dd, 6 - (@@Datefirst + Datepart(dw, @D) ) %7 , @D) as NextFriday

Will return next Friday no matter what.

Even if it's first Friday of next year.

_____________
Code for TallyGenerator

goodguy SSCertifiable Points: 5456 More actions · Answer 7

Sergiy, your solution as usual is brilliant, but if you check back the OP wants to process an argument that is in yyyyww format.

Regards,

goodguy

Experience is a bad teacher whose exams precede its lessons

Sergiy SSC Guru Points: 110208 More actions · Answer 8

I saw at least 3 different ways of interpreting "22nd week of the year".

I believe OP knows better when Nth week starts and when it finishes, so it's up to OP to convert YYYYWW to the actual date.

There's nothing to do about it with T-SQL.

_____________
Code for TallyGenerator

goodguy SSCertifiable Points: 5456 More actions · Answer 9

I am a greenhorn in SQL, so I hope you understand that I asked out of curiosity, not to show off. No offense intended.

Regards,

goodguy

Experience is a bad teacher whose exams precede its lessons

Sergiy SSC Guru Points: 110208 More actions · Answer 10

Actually I did not find there any attempt of offence from your side.

I just explained why I took it from the point of known date and did not show how to get actual date from YYYYMM.

Stay cool.

Keep annoying brilliant people with questions out of curiosity.

_____________
Code for TallyGenerator

Carl Federl One Orange Chip Points: 25384 More actions · Answer 11

Since Sergiy did the hard part, here is an extention to use year and week:

create table #YWK

(yyyyww char(6) not null )

insert into #YWK

(yyyyww )

select '200701' union select '200726' union select '200752' union

select '200753' union select '200754' union select '200755'

SELECT yyyyww

,MondayDt

-- Sergiy

, DATEADD(dd, 6 - (@@Datefirst + Datepart(dw, MondayDt) ) %7 , MondayDt) as NextFriday

FROM

-- Carl get monday's date for any week number

( select yyyyww

, DATEADD( dd

, ( cast ( substring(yyyyww,5,2) as integer ) - 1 ) * 7

, cast ( substring(yyyyww,1,4) + '-01-01' as datetime ) )

as MondayDt

from #YWK

) as X

SQL = Scarcely Qualifies as a Language

Carl Federl One Orange Chip Points: 25384 More actions · Answer 12

Sorry, the column Monday's date is incorrect and needs to be a variation of Sergiy's Friday algorithm:

if object_id('tempdb..#YWK') is not null drop table #YWK

create table #YWK

(yyyyww char(6) not null )

insert into #YWK

(yyyyww )

select '200701' union select '200702' union select '200726' union select '200752' union

select '200753' union select '200754' union select '200755' union

select '200801' union select '200802' union select '200826' union select '200852' union

select '200853' union select '200854' union select '200855'

SELECT yyyyww

, DATEADD(dd, 2 - (@@Datefirst + Datepart(dw, WkDt) ) %7 , WkDt) as ThisMonday

-- Sergiy

, DATEADD(dd, 6 - (@@Datefirst + Datepart(dw, WkDt) ) %7 , WkDt) as NextFriday

FROM

-- Carl get monday's date for any week number

( select yyyyww

, DATEADD( dd

, ( cast ( substring(yyyyww,5,2) as integer ) - 1 ) * 7

, cast ( substring(yyyyww,1,4) + '-01-01' as datetime ) )

as WkDt

from #YWK

) as X

SQL = Scarcely Qualifies as a Language

goodguy SSCertifiable Points: 5456 More actions · Answer 13

Nice touch.

Thanks Carl Serge.

Carl, if you can get ThisMonday from your code, can't you short-circuit it to get NextFriday too? I tried it, works fine.

if object_id('tempdb..#YWK') is not null drop table #YWK

create table #YWK

( yyyyww char(6) not null )

insert into #YWK

(yyyyww )

select '200701' union select '200702' union select '200726' union select '200752' union

select '200753' union select '200754' union select '200755' union

select '200801' union select '200802' union select '200826' union select '200852' union

select '200853' union select '200854' union select '200855'

select yyyyww

, DATEADD( dd

, ( cast ( substring(yyyyww,5,2) as integer ) - 1 ) * 7

, cast ( substring(yyyyww,1,4) + '-01-05' as datetime ) )

as WkDt

from #YWK

Regards,

goodguy

Experience is a bad teacher whose exams precede its lessons

Theo Jacobs Say Hey Kid Points: 694 More actions · Answer 14

Tank you to all for the help provided. I used a combination of te solutions provided to come up with the solution required + a little customization. In the end it works better than I could have hoped for.

Thanx again. Seems to me that I will have to start using these forums more often. It is such a wealth of tech knowledge.

Happy codeing

T