How to get one month ago from today in SQL Server 2008? -

i´m writing query last month, time in zeros (if today 2013-05-21 want 2013-04-21 00:00:00.000).

so tried:

select (dateadd(month,datediff(month,(0),getdate())-1,(0))); 

but first day of previous month.

then tried:

select dateadd(month, -1, getdate()); 

i right day, current time (2013-04-21 11:41:31.090), , want time in zeros.

so how should query in order like: 2013-04-21 00:00:00.000

thanks in advance.

in sql server 2008 there date data type, has no time attached. can remove time portion quite converting, performing dateadd.

select dateadd(month, -1, convert(date, getdate())); 

this return date data type. force datetime again, can add 1 more convert:

select convert(datetime, dateadd(month, -1, convert(date, getdate()))); 

you may not need explicit conversion datetime, though.

note: "one month ago today" defined in many different ways. way works in sql server return day previous month closest same day number current month. means result of expression when run on march 31 february 28. so, may not expected results in scenarios if don't think ramifications of this, such if performed one-month calculation multiple times, expecting same day in different month (such doing march -> february -> january).

see live demo @ sql fiddle

the demo shows values , resulting data types of each expression.