Getting last week same day ,last month same day etc date for today.

hi all ,

i have a variable @.date = '06/26/2007 00.00.00.000' i.e. today's date.

I have to find out same day's( i.e. tuesday ) date last week , last month and last year.

for last week same day @.lastweekday = @.date-7 is working fine.

but how to get same week tuesday for last month and last year.

i.e. how can i extract out 05/29/2007 00.00.00.000 i.e. last month same week tuesday and '06/27/2006 00.00.00.000' ie.tuesday last year same month same week. from @.date ?

Thanks for quick replies :-)So you want to say "I want the 4th Tuesday" ?

I think May 29th is the 5th Tuesday, so perhaps I'm wrong.

You can use dateadd(month,-1,@.date) to get a month ago, datepart(dw,@.date) to get the weekday, and @.@.datefirst to get what the first day of the week is. I would suggest trying a few options with these values to see if you can get the answer you want - but it's going to depend on what logic you're actually after.

Rob|||

here you go...

Code Snippet

Create Function dbo.GetNthWeekDay

(

@.Input Datetime,

@.WeekDay int,

@.N int

) Returns datetime

as

Begin

Declare @.cd as int

Declare @.sunday as datetime

Declare @.CurrWeekDayDate as datetime

Set @.cd = datepart(w, @.Input)

Set @.sunday = DateAdd(DD, 1-@.cd, @.Input)

Select @.CurrWeekDayDate = DateAdd(DD, @.WeekDay-1, @.sunday)

return Dateadd(wk, @.n, @.CurrWeekDayDate)

End

Go

Select dbo.GetNthWeekDay(getdate(), 1, -2) -- Monday 2 weeks before

Select dbo.GetNthWeekDay(getdate(), 4, 2) -- Thursday 2 weeks after

Select dbo.GetNthWeekDay(getdate(), 4, 0) -- Friday current week

Select dbo.GetNthWeekDay(getdate(), 1, 0) -- Sunday current week

Select dbo.GetNthWeekDay(getdate(), 1, 1) -- Sunday Next week

--Ok Your wish list here

Select dbo.GetNthWeekDay(dateadd(mm,-1,getdate()), 4, 0) -- For Last Month

Select dbo.GetNthWeekDay(dateadd(yy,-1,getdate()), 4, 0) -- For Last Year

|||

Thanks Mani & Rob for quick replies.

Mani i tried with the code , i m facing one problem .

Like if u try this :

declare @.date datetime

declare @.noofday int

set @.date = '2007-06-25 00:00:00.000'

set @.noofday =DATEPART(weekday,@.date)

select dbo.GetNthWeekDay(dateadd(mm,-1,@.date),@.noofday,0)

This one is returning 21st May 2007, but it shud return 28th May 2007 as the 4th Monday last month corresponding to 25th June 2007.

|||

another alternative, if you already have the calendar udf

you can do this by

select a.*
from dbo.GetCalendarDates(@.from,@.to) a inner join
dbo.GetCalendarDates(@.thedate,@.thedate) b on
( a.[year] = b.[year] - 1
and a.[month] = b.[month]
and a.weekofmonth = b.weekofmonth
and a.dayofweek = b.dayofweek ) -- last year
or ( a.[year] = b.[year]
and a.[month] = b.[month] - 1
and a.weekofmonth = b.weekofmonth
and a.dayofweek = b.dayofweek ) -- last month
or a.CalendarDate = @.thedate -- your var date