last month last week and today

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 2005 Forums

Transact-SQL (2005)

last month last week and today

Author

Topic

jamie
Aged Yak Warrior

542 Posts

Posted - 2008-01-09 : 08:48:50

hi guys,

today is wednesday,
what is the sql code to display last weeks wednesday, and also the same wednesday last month ( eg, 2nd wed in dec ).

I need this to be realy time, so tomorrow show .
thursday, last thursday and last months thursday.

kindest regards,
ja

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-09 : 08:51:05

select getdate(), dateadd(week,-1,getdate()),dateadd(week,-4,getdate())

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-09 : 08:51:46

SELECT DATEADD(DAY, -7, CURRENT_TIMESTAMP),
DATEADD(DAY, -14, CURRENT_TIMESTAMP)

E 12°55'05.25"
N 56°04'39.16"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-09 : 08:53:40

what if you are on the 5th week of the month ? which date of last month do you want to show ?


select	today, 		
	week_day 	= datename(weekday, today),
	last_week	= dateadd(day, -7, today),
	last_month	= dateadd(day, -28, today)
from	
(
	select	today = dateadd(day, datediff(day, 0, getdate()), 0)
) d

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-09 : 08:54:13

[damn]

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-01-09 : 13:16:57

Khtan is right. You'd, probably, have to do some logic around the "same day last month" logic.

What would you want for 2008-12-30?

DECLARE @Date DATETIME
SET @Date = '2008-12-30' -- Tuesday

SELECT 
	@Date, 
	DATEPART(WEEKDAY, @Date),
	DATEADD(WEEK, -4, @Date), 
	DATEPART(WEEKDAY, DATEADD(WEEK, -4, @Date)),
	DATEADD(MONTH, -1, @Date),
	DATEPART(WEEKDAY, DATEADD(MONTH, -1, @Date))

SELECT DATEPART(WEEK, @Date)

jamie
Aged Yak Warrior

542 Posts

Posted - 2008-01-11 : 06:51:50

cheers geniuses !
This works great

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources