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

Question

I´m writing a query where i get the last month, but with the time in zeros (if today is 2013-05-21 then i want to get 2013-04-21 00:00:00.000).

So I tried:

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

But I get the first day of the previous month.

Then I tried:

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

I get the right day, but I also get the current time (2013-04-21 11:41:31.090), and I want the time in zeros.

So how should my query be in order to get something like: 2013-04-21 00:00:00.000

Thanks in advance.

Answer 1

In SQL Server 2008 there is the date data type, which has no time attached. You can thus remove the time portion quite easily simply by converting, then performing the DateAdd.

SELECT DateAdd(month, -1, Convert(date, GetDate()));

This will return a date data type. To force it to be datetime again, you can simply add one more Convert:

SELECT Convert(datetime, DateAdd(month, -1, Convert(date, GetDate())));

You may not need the explicit conversion to datetime, though.

Note: "One month ago from today" could be defined in many different ways. The way it works in SQL server is to return the day from the previous month that is the closest to the same day number as the current month. This means that the result of this expression when run on March 31 will be February 28. So, you may not get expected results in certain scenarios if you don't think clearly about the ramifications of this, such as if you performed the one-month calculation multiple times, expecting to get the same day in a different month (such as doing March -> February -> January).

See a live demo at SQL Fiddle

The demo shows the values and resulting data types of each expression.