DateAdd

Hello Everyone,
I trying to build an SQL statement that includes the last twelve
months in the report. When I use the DATEADD function ie >= DATEADD(MM, - 12, GETDATE())) the report brings back data 12 months
from the current date. I would like to include the whole month not
just the current date. I hope this make sense!
R/ A. AkinHello awakin,
You need to take of the day of the month as well day(getdate()) returns the
day of the month but the simplest is to build the date from scratch i.e
convert(datetime, cast((year(getdate())-1) *100 + month(getdate())as char(6))
+ '01' ,112)
This builds a date string in the ISO format yyyymmdd and then converts it
to a date using the relevant coversion style for the ISO format
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
> Hello Everyone,
> I trying to build an SQL statement that includes the last twelve
> months in the report. When I use the DATEADD function ie >=> DATEADD(MM, - 12, GETDATE())) the report brings back data 12 months
> from the current date. I would like to include the whole month not
> just the current date. I hope this make sense!
> R/ A. Akin
>|||On Sep 30, 3:47 am, Simon Sabin <SimonSa...@.noemail.noemail> wrote:
> Hello awakin,
> You need to take of the day of the month as well day(getdate()) returns the
> day of the month but the simplest is to build the date from scratch i.e
> convert(datetime, cast((year(getdate())-1) *100 + month(getdate())as char(6))
> + '01' ,112)
> This builds a date string in the ISO format yyyymmdd and then converts it
> to a date using the relevant coversion style for the ISO format
> Simon Sabin
> SQL Server MVPhttp://sqlblogcasts.com/blogs/simons
>
> > Hello Everyone,
> > I trying to build an SQL statement that includes the last twelve
> > months in the report. When I use the DATEADD function ie >=> > DATEADD(MM, - 12, GETDATE())) the report brings back data 12 months
> > from the current date. I would like to include the whole month not
> > just the current date. I hope this make sense!
> > R/ A. Akin- Hide quoted text -
> - Show quoted text -
Simon,
Thanks for the response; here is what worked for me.
>= DATEADD(MM, - 12, CAST(CAST(YEAR(GETDATE()) AS VARCHAR) + ' - ' +
CAST(MONTH(GETDATE()) AS VARCHAR) + '-01' AS DATETIME)))
This allowed me to return the appropriate data.
r/ A.Akin