How to find previous year dates

I just created a function which does what you need

alter FUNCTION fnBala (
	@Year varchar(20),
	@Month varchar(20),
	@Day2Find varchar(20),
	@nth int
)
RETURNS date
AS
BEGIN
	
	-- select dbo.fnBala ('2012','JAN','MON',1)
	Declare @StartDate date =convert(date,'1/'+@Month+'/'+@Year)
	Declare @FirstDateOfMonth date
	
	Declare @Factor int=-1

	
	if @Day2Find='SUN' 		set @Factor=6
	else if @Day2Find='SAT'		set @Factor=5
	else if @Day2Find='MON'		set @Factor=0
	else if @Day2Find='TUE'		set @Factor=1
	else if @Day2Find='WED'		set @Factor=2
	else if @Day2Find='THU'		set @Factor=3
	else if @Day2Find='FRI'		set @Factor=4
	
		
	if  ( @Factor - DATEDIFF(DD,0,@StartDate)%7) <0
		set @FirstDateOfMonth=dateadd("d",( @Factor - DATEDIFF(DD,0,@StartDate)%7)+7,@StartDate)	
	else		
		set @FirstDateOfMonth=dateadd("d",( @Factor - DATEDIFF(DD,0,@StartDate)%7),@StartDate)
	
	return dateadd("d",7*(@nth-1),@FirstDateOfMonth)
	
END

Fist time when you are creating the function

alter FUNCTION fnBala

needs to be repalced by

create FUNCTION fnBala 

1 If you want to find if feb2010 month 1st sunday is what date?

select dbo.fnBala ('2010','FEB','SUN',1)

2. If you want to find feb2011 3rd sunday is what date?

select dbo.fnBala ('2011','FEB','SUN',3)

I just tested this on SQL server 2008 and it works for me and not suree what is your SQL server version and please let me know if you get any error message along with the SQL server version


Hope this helps

As I understood your question you want for example to know in Feb 2010 what is the date of first Sunday.
So here is one solution :

declare @y int
set @y = 2010  --in which year we are interested ?
declare @m int
set @m = 2 --February 
declare @d int
set @d = 1  -- start from first day of month
declare @dp int
set @dp = 0  --this is an invalid day of the week ! untill we reach to desired one

--finding first sunday in Feb 2010
set @dp = datepart(dw,	CAST(CAST(@y AS varchar) + '-' + CAST(@m AS varchar) + '-' + CAST(@d AS varchar) AS DATETIME) )
while @dp!=1 --search for sunday
begin
	set @d = @d+1	
	set @dp = datepart(dw,	CAST(CAST(@y AS varchar) + '-' + CAST(@m AS varchar) + '-' + CAST(@d AS varchar) AS DATETIME) )
end
print CAST(CAST(@y AS varchar) + '-' + CAST(@m AS varchar) + '-' + CAST(@d AS varchar) AS DATETIME)

It need some customizations to meet your needs but I'm sure you can handle that.

Please mark it as an answer if it solved your problem or vote it up :)

use below script to get 1 year back exact date of getdate()

Declare @NoOfYearBefore/AfterDate
Set @NoOfYearBefore/AfterDate=-1


Select DateAdd(year, @NoOfYearBefore/AfterDate, getdate())