get last one week day and 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

General SQL Server Forums

New to SQL Server Programming

get last one week day and date

Author

Topic

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2010-03-17 : 04:50:49

Hi,

if i give input as date then i need last seven day,date as result.
for example if i give 03/17/2010 then my input will be

03/17/2010 03/16/2010 03/15/2010 03/14/2010 03/13/2010 03/12/2010
Wednesday Tuesday Monday Sunday Saturday Friday

03/11/2010
Thursday

please give me sample query yo achieve this. since i am wondering could it be achieved using query. please help me in this

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-17 : 04:53:20

Let us know,if this helps

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=105369

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-17 : 04:58:53

[code]
declare @date datetime

select @date = '20100317'

select @date,
dateadd(day, -1, @date),
dateadd(day, -2, @date),
dateadd(day, -3, @date),
dateadd(day, -4, @date),
dateadd(day, -5, @date),
dateadd(day, -6, @date)

select datename(weekday, @date),
datename(weekday, dateadd(day, -1, @date)),
datename(weekday, dateadd(day, -2, @date)),
datename(weekday, dateadd(day, -3, @date)),
datename(weekday, dateadd(day, -4, @date)),
datename(weekday, dateadd(day, -5, @date)),
datename(weekday, dateadd(day, -6, @date))
[/code]

KH
[spoiler]Time is always against us[/spoiler]

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2010-03-17 : 05:02:55

thanks a lot khtan.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-03-17 : 05:41:27

This is more generalize solution



DECLARE @date DATETIME
DECLARE @tmpdate DATETIME
DECLARE @tbl AS TABLE (dt DATETIME, day VARCHAR(20))

SELECT @date = '20100317'
SELECT @tmpdate = @date

WHILE @tmpdate > DATEADD(DAY,-7, @date)
BEGIN
	INSERT INTO @tbl
	SELECT @tmpdate, DATENAME(WEEKDAY, @tmpdate)
	SET @tmpdate = DATEADD(DAY,-1, @tmpdate)
END
SELECT * FROM @tbl

Vaibhav T

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources