list 7 days before the given date

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

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

list 7 days before the given date

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-12-08 : 09:00:13

Kivanc writes "As you can understand from the subject I wonder how can list 7 days before the given date? lets say I used getdate and found that it is 1.1.2006, the script should give me

31.12.2006
30.12.2006
29.12.2006
28.12.2006
27.12.2006
26.12.2006
25.12.2006

Thanks for your help"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-08 : 09:02:55

Select columns from table
Where datecol>=Dateadd(day,Datediff(day,0,Getdate()),-7)
and datecol<Dateadd(day,Datediff(day,0,Getdate()),0)

Madhivanan

Failing to plan is Planning to fail

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-08 : 09:27:27

[code]SELECT DATEADD(DAY, -(T.NUM), GETDATE())
FROM
(
SELECT (NUMBER + 1) AS NUM
FROM MASTER..SPT_VALUES
WHERE NAME IS NULL
AND NUMBER < 7
) T[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-08 : 14:18:09

Hello Master Spt_Values!
Long time no see...

Little more simplified, and with time information removed

SELECT		DATEADD(day, DATEDIFF(day, Number, GETDATE()), -1)
FROM		master..spt_values
WHERE		Name IS NULL
		AND Number < 7
ORDER BY	Number

Kivanc, the expected output you provide is based on a date of 1.1.2007, not 1.1.2006

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-08 : 14:23:47

At least I learned something from you, Peter!

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-08 : 14:46:51

You first now learned how to proof-read sample data?

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-09 : 02:55:27

quote:
Originally posted by Peso

You first now learned how to proof-read sample data?

Peter Larsson
Helsingborg, Sweden

I think I didnt read the question correctly

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-09 : 05:44:56

I was just kidding with Harsh. He'll understand.
Also I think he referred to the use of master..spt_values.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources