Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 previous 28 weeks
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

4 Posts

Posted - 07/14/2004 :  21:18:56  Show Profile  Reply with Quote
Hi Friends,

I am using Sqlserver 2000, i have a column "Week_End_Date" that stores all the week end dates like 07/07/2004,07/14/2004.
i want a query to get previous 28 weeks from current week. and i need this week format like mm/dd(month/day i.e 07/07, 07/14) i need the following output.
06/23 .................upto prevous 28 week

Regards, Amr

Flowing Fount of Yak Knowledge

1242 Posts

Posted - 07/14/2004 :  21:43:46  Show Profile  Visit timmy's Homepage  Reply with Quote

SELECT Convert(varchar, month(Week_End_Date)) + '/' + Convert(varchar, day(Week_End_Date))
FROM table
WHERE Week_End_Date BETWEEN DateAdd(d, -196, GetDate()) and GetDate()

Go to Top of Page

Posting Yak Master

United Kingdom
158 Posts

Posted - 07/15/2004 :  05:37:42  Show Profile  Reply with Quote

SELECT TOP 28 LEFT(CONVERT(varchar,week_end_date,1),5)
ORDER BY week_end_date DESC
Go to Top of Page

Starting Member

4 Posts

Posted - 07/15/2004 :  08:16:23  Show Profile  Reply with Quote

Thanks for your quick response, I have the week format(mm/dd) from Week_end_date(07/14/2004).

I want to get 28 previous weeks in the formartof week(mm/dd).

Regards, Amr
Go to Top of Page

Starting Member

4 Posts

Posted - 07/15/2004 :  08:31:39  Show Profile  Reply with Quote
Hi jasongof

Thanks for your response

I tried your code its working but I am not getting the latest 28 weeks data, If i use top 28 it's giving the 28 weeks but not the previous 28 weeks. any help would be appreciated

Regards, Amr
Go to Top of Page

Not Just a Number

15586 Posts

Posted - 07/15/2004 :  09:32:57  Show Profile  Reply with Quote
I'm not sure I understand....

Do you want to figure out the end dates?

DECLARE @x datetime, @y int, @z int
SELECT @x = '07/24/2004', @y = -7, @z = 1

DECLARE @d TABLE(prev_dates datetime)

WHILE @z < 28
	INSERT INTO @d(prev_dates) SELECT DATEADD(d,@y,@x)
	SELECT @y = @y - 7, @z = @z + 1

Or do you want to find that specific day, or what about the days in between...

I'm so confused...


Go to Top of Page

Flowing Fount of Yak Knowledge

2878 Posts

Posted - 07/15/2004 :  09:49:31  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
Does this work for you??

Declare @seq table (n int)
Insert Into @Seq
Select 0 Union All Select 1 Union All Select 2 Union All Select 3 Union All Select 4 Union All Select 5 Union All Select 6 Union All Select 7 Union All Select 8 Union All Select 9 Union All Select 10 Union All 
Select 11 Union All Select 12 Union All Select 13 Union All Select 14 Union All Select 15 Union All Select 16 Union All Select 17 Union All Select 18 Union All Select 19 Union All Select 20 Union All
Select 21 Union All Select 22 Union All Select 23 Union All Select 24 Union All Select 25 Union All Select 26 Union All Select 27 Union All Select 28

Declare @periodEnd datetime

Set @periodEnd = '7/14/2004'

Select periodEnd = left(convert(nvarchar,dateAdd(dy,-7*n,@periodEnd),101),5) From @seq

If you don't want the current period then remove the 'Select 0 Union All' near the top.

Go to Top of Page

Dr. Cross Join

7423 Posts

Posted - 07/15/2004 :  10:23:58  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
you have not given us enough info. you have a column called "Week_end_Date" which is great, but in what table? what is the PK of that table? what kind of data are you trying to return? do you just want a list of week-ending dates, or do you want all data within that timeperiod?

how about a CREATE TABLE statement, some sample data, and what you would like to have returned.

is it as simple as:

select * from
where Week_Ending_Date between datediff(w, 28, getdate()) and getdate()


- Jeff
Go to Top of Page

Starting Member

4 Posts

Posted - 07/15/2004 :  12:44:28  Show Profile  Reply with Quote
Hi JSmith

Thanks for your response.

Here is the sample code.
2003-09-03 00:00:00
2003-09-24 00:00:00
2003-10-01 00:00:00
2003-10-15 00:00:00
2003-10-22 00:00:00
2004-02-04 00:00:00
2004-02-11 00:00:00
2004-03-17 00:00:00
2004-03-31 00:00:00
2004-04-14 00:00:00
2004-04-21 00:00:00
2004-05-05 00:00:00
2004-06-23 00:00:00
2004-06-30 00:00:00
2004-07-07 00:00:00
2004-07-14 00:00:00
2004-08-04 00:00:00

these are all week end dates . I just want to display only previous 28 week end dates from current week end date.for example(
2004-06-23 upto previous 28 week end dates.

regards, Amr

Go to Top of Page

Flowing Fount of Yak Knowledge

1242 Posts

Posted - 07/18/2004 :  19:34:37  Show Profile  Visit timmy's Homepage  Reply with Quote

You can calculate the week-end date for any date by using the following formula:

SET @weekEndDate = dateadd(d, 6-DatePart(w, @d), @d)

where: @d is the date in question and
6 is the day of the week (Friday in this case). 5=Thur, 4=Wed, etc

Plug this back into your formula and see how you go...

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2019 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000