Manipulating dates in SQL

I need to generate a date range, based on the current date (or an input date). I can get the correct dates using VB, but I haven't worked out the TSQL Syntax for them yet. Can anyone tell me the TSQL syntax for manipulating dates in the following way...?

Start Date... This is the first day of the month, one year ago... in VB I worked it out as...

dateadd("yyyy",-1,(cdate(cstr(Year(now))+"-"+cstr(Month(now))+"-01")))

End Date... This is the last day of the previous month... in VB I worked this one out as...

dateadd("d",-1,(cdate(cstr(Year(now))+"-"+cstr(Month(now))+"-01")))

eg. for today 18/01/2007 I would get a Start date of 01/01/2006 and an End date of 31/12/2006

Any help would be appreciated.

I managed to work out a solution... I get my Start date by using the following...

(select dateadd(yyyy,-1,(select stuff(stuff((convert(varchar,
convert(varchar,datepart(year,getdate()))+
convert(varchar,datepart(Month,getdate()))+
convert(varchar,(convert(int,datepart(day,getdate())))- (convert(int,datepart(day,getdate()))-1))))
,5 ,0, '-'),7,0, '-'))))

|||

Hey Jon,

This should do what you want it to.

select dateadd(mm, datediff(mm, 0, dateadd(yy, -1, getdate())), 0) as StartDate,
dateadd(dd, -1, dateadd(mm, datediff(mm, 0, getdate()), 0)) as EndDate

Hope this helps.

Jarret

|||

That's great... thanks Jarret.

A much better solution than mine... especially since I noticed mine only works if the month is a single digit.