i need logic to get the days for most recent week that ends with Friday

Question

Post reply

i need logic to get the days for most recent week that ends with Friday

purushotham.k9

SSCommitted

Points: 1992
More actions
December 21, 2009 at 5:53 am

#136865

can some please let me kow the procedure to get the most week days(i.e the most recent firiday is 18th So i need to get the days like 12,13,14,15,16,17 and 18.

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

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

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 1

Lynn Pettis

SSC Guru

Points: 442447

December 21, 2009 at 6:03 am

#1094692

Something like this?



declare @TestDate datetime;

set @TestDate = '2009-12-19';

select dateadd(wk,datediff(wk,0,@TestDate + 1),-9),dateadd(wk,datediff(wk,0,@TestDate + 1),-3)

set @TestDate = '2009-12-24';

select dateadd(wk,datediff(wk,0,@TestDate + 1),-9),dateadd(wk,datediff(wk,0,@TestDate + 1),-3)

Lowell SSC Guru Points: 323482 More actions · Answer 2

dates can seem tricky until you get used to them.

never convert a date to varchar or anything...always keep them in datetime .

this will give you the Monday of the current week; if you need LAST monday, you'd deduct one more week from it for monday, and add 5 days to it to get that friday:



--Monday of the Current Week

select DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)

--Monday of "Last Week":

select DATEADD(wk,-1,DATEADD(wk, DATEDIFF(wk,0,getdate()),0))

--FRIDAY of "Last Week":(Monday minus 3 days, or Monday +4

select DATEADD(dd,-3,DATEADD(wk, DATEDIFF(wk,0,getdate()),0))

select DATEADD(dd,4,DATEADD(wk,-1,DATEADD(wk, DATEDIFF(wk,0,getdate()),0)))

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Old McDonald SSCertifiable Points: 5768 More actions · Answer 3

Old McDonald

SSCertifiable

Points: 5768

December 21, 2009 at 6:14 am

#1094695



declare @lastFriday datetime

SET @lastFriday = convert(datetime,convert(varchar(10),getdate() - (datepart(w,getdate())+1),121))

select

@lastFriday - 6

,@lastFriday - 5

,@lastFriday - 4

,@lastFriday - 3

,@lastFriday - 2

,@lastFriday - 1

,@lastFriday

-Vikas Bindra

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 4

vikas bindra (12/21/2009)

declare @lastFriday datetime SET @lastFriday = convert(datetime,convert(varchar(10),getdate() - (datepart(w,getdate())+1),121)) select @lastFriday - 6 ,@lastFriday - 5 ,@lastFriday - 4 ,@lastFriday - 3 ,@lastFriday - 2 ,@lastFriday - 1
,@lastFriday

You don't need to convert to character format. Keep your date calculations using datetime values.

Nabha SSCrazy Eights Points: 8534 More actions · Answer 5

Nabha

SSCrazy Eights

Points: 8534

December 21, 2009 at 6:26 am

#1094701

All good answers above,

one more way to find your last friday, (and then deduce your other days)

Select dateadd(day, - (datepart(dw, getdate())+1), getdate())

---------------------------------------------------------------------------------

Old McDonald SSCertifiable Points: 5768 More actions · Answer 6

Lynn Pettis (12/21/2009)

vikas bindra (12/21/2009)

declare @lastFriday datetime SET @lastFriday = convert(datetime,convert(varchar(10),getdate() - (datepart(w,getdate())+1),121)) select @lastFriday - 6 ,@lastFriday - 5 ,@lastFriday - 4 ,@lastFriday - 3 ,@lastFriday - 2 ,@lastFriday - 1
,@lastFriday

You don't need to convert to character format. Keep your date calculations using datetime values.

I am converting to varchar to ignore the time part and get only the date.

Is there any other to remove the time part?

-Vikas Bindra

Nabha SSCrazy Eights Points: 8534 More actions · Answer 7

Nabha

SSCrazy Eights

Points: 8534

December 21, 2009 at 6:33 am

#1094708

Is there any other to remove the time part?

Select dateadd(day, 0, datediff(day, 0, getdate()))

---------------------------------------------------------------------------------

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 8

vikas bindra (12/21/2009)

Lynn Pettis (12/21/2009)

vikas bindra (12/21/2009)

declare @lastFriday datetime SET @lastFriday = convert(datetime,convert(varchar(10),getdate() - (datepart(w,getdate())+1),121)) select @lastFriday - 6 ,@lastFriday - 5 ,@lastFriday - 4 ,@lastFriday - 3 ,@lastFriday - 2 ,@lastFriday - 1
,@lastFriday

You don't need to convert to character format. Keep your date calculations using datetime values.

I am converting to varchar to ignore the time part and get only the date.

Is there any other to remove the time part?

Don't need to ignore the time portion. For one, check out the code I posted. It doesn't care what the time portion is. Also, you drop the the time like this:

dateadd(dd, datediff(dd, 0, getdate()), 0)

Old McDonald SSCertifiable Points: 5768 More actions · Answer 9

vikas bindra (12/21/2009)

Lynn Pettis (12/21/2009)

vikas bindra (12/21/2009)

declare @lastFriday datetime SET @lastFriday = convert(datetime,convert(varchar(10),getdate() - (datepart(w,getdate())+1),121)) select @lastFriday - 6 ,@lastFriday - 5 ,@lastFriday - 4 ,@lastFriday - 3 ,@lastFriday - 2 ,@lastFriday - 1
,@lastFriday

You don't need to convert to character format. Keep your date calculations using datetime values.

I am converting to varchar to ignore the time part and get only the date.

Is there any other to remove the time part?

Cool. Thanks for showing this. it looks better than converting to varchar

-Vikas Bindra

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 10

vikas bindra (12/21/2009)

vikas bindra (12/21/2009)

Lynn Pettis (12/21/2009)

vikas bindra (12/21/2009)

declare @lastFriday datetime SET @lastFriday = convert(datetime,convert(varchar(10),getdate() - (datepart(w,getdate())+1),121)) select @lastFriday - 6 ,@lastFriday - 5 ,@lastFriday - 4 ,@lastFriday - 3 ,@lastFriday - 2 ,@lastFriday - 1
,@lastFriday

You don't need to convert to character format. Keep your date calculations using datetime values.

I am converting to varchar to ignore the time part and get only the date.

Is there any other to remove the time part?

Cool. Thanks for showing this. it looks better than converting to varchar

You can also check out my blog post Some Common Date Routines.

Old McDonald SSCertifiable Points: 5768 More actions · Answer 11

Old McDonald

SSCertifiable

Points: 5768

December 21, 2009 at 7:16 am

#1094739

Thanks Lynn!

-Vikas Bindra

purushotham.k9 SSCommitted Points: 1992 More actions · Answer 12

purushotham.k9

SSCommitted

Points: 1992

December 21, 2009 at 7:18 am

#1094742

I have got the expected result, but i need to display the header like Dec 18 2009, dec 17 2009.....dec 12 2009..Can you please help me to get in this format

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 13

Lynn Pettis

SSC Guru

Points: 442447

December 21, 2009 at 7:27 am

#1094751

How about you show us what you have tried first. Maybe we'll what you are doing wrong and help you that way instead of just giving you the answer.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 14

vikas bindra (12/21/2009)

Cool. Thanks for showing this. it looks better than converting to varchar

Over a fair number of rows, it's also quite a bit faster because it doesn't have to go through a string based conversion.

--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)
Intro to Tally Tables and Functions