need a logic to find out the previous week for previous week for any given date

Question

need a logic to find out the previous week for previous week for any given date

purushotham.k9

SSCommitted

Points: 1992
More actions
May 14, 2009 at 9:11 am

#130098

I need to find out the previous week for any given date in SQL server. i need help

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

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

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

Please define what you mean by the previous week. Also, go to my blog (SQL Musings from the Desert), and you will find some common date computation formulas.

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

currently i am generating the report for all the weeks. now i would like to generate the report only for previous week in any given date for that i need SQL

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Doesn't answer the question. What is the previous week? Does it start on Monday, Tuesday, Sunday? Define what the previous week is. Also, take a look at the functions I alluded to above on my blog, I think they will help you.

Also, please read the first article I reference below in my signature block.

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

actuvally there are 2 reports One for all the dates, another for data just from last week. By last week I mean a previous week for any given date.

suppose if i run the report today i have to get the last week report like 05/03-05/09 week report i want

daniel.gardiner Old Hand Points: 305 More actions · Answer 5

I'm not certain of the SQL functions but is it possible to:

subtract 7 days from the current date

determine the 'week' part of this date

select records with dates from this 'week'

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

daniel.gardiner (5/14/2009)
I'm not certain of the SQL functions but is it possible to:
subtract 7 days from the current date
determine the 'week' part of this date
select records with dates from this 'week'

What do you call a week? Sunday through Saturday, Monday through Sunday, please define a week.

daniel.gardiner Old Hand Points: 305 More actions · Answer 7

I don't define 'week', that's determined by SQL, and a quick Google search shows that the first day of the week can be changed with SET DATEFIRST. The original poster's last response shows that they want a week that starts with Sunday (05/03/09), so a week to them is Sunday through Saturday.

The 'week' value I was referring to is the value returned by the DATEPART function, which is a numerical value representing the week of the year.

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

But that may not be the "week" that the OP wants to report. That is why it is important to know what is meant by the previous week.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

purushotham.k9 (5/14/2009)
actuvally there are 2 reports One for all the dates, another for data just from last week. By last week I mean a previous week for any given date.
suppose if i run the report today i have to get the last week report like 05/03-05/09 week report i want

So your week is defined as Sunday through Saturday, correct?

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

The following sample code should get you started.

declare @ThisDate datetime;

set @ThisDate = getdate();

select dateadd(wk, datediff(wk, 0, @ThisDate) - 1, -1) -- Beginning of last week (Sunday)

select dateadd(wk, datediff(wk, 0, @ThisDate), -1) -- Beginning of this week (Sunday)

select

yt.*

from

dbo.YourTable yt

where

yt.SelectDateColumn >= dateadd(wk, datediff(wk, 0, @ThisDate) - 1, -1)

and yt.SelectDateColumn < dateadd(wk, datediff(wk, 0, @ThisDate), -1);