MOHIT_NITDGP


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 :-)



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

Rob Farley


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





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

Manivannan.D.Sekaran

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







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

MOHIT_NITDGP

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.





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

[rh4m1ll3] Rhamille Golimlim

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