sqlsql-serversql-server-2008

How can I get the last 12 months from the current date PLUS extra days till 1st of the last month retrieved

Getting the last 12 months from a specific date is easy and can be retrieved by the following command in SQL-server. Its answer is 2014-08-17.

select Dateadd(Month, -12, '2015-08-17')

What I want is to get the last 12 months but ending at 2014-08-01 (in the above case) instead of any where in the middle of the month.

Solution

  • Using DATEADD and DATEDIFF:

    DECLARE @ThisDate DATE = '20150817'
SELECT DATEADD(YEAR, -1, DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @ThisDate), '19000101'))

    For more common date routines, see this article by Lynn Pettis.

    To use in your WHERE clause:

    DECLARE @ThisDate DATE = '20150817'
SELECT *
FROM <your_table>
WHERE
    <date_column> >= DATEADD(YEAR, -1, DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @ThisDate), '19000101'))