CODEWITHSUNDEEP
sqlsql-serversql-server-2008
drmaa
drmaa

Reputation: 3684

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.

Upvotes: 8

Views: 113052

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270573

If you want all the records since the first day of the current month last year, then you can use:

where <somedate> >= dateadd(day, 1 - day(dateadd(month, -12, getdate())),
                            dateadd(month, -12, getdate()))

For all days except Feb 29th, you can use the simpler:

where <somedate> >= dateadd(day, 1 - day(getdate()),
                            dateadd(month, -12, getdate))

Upvotes: 1

Madhivanan
Madhivanan

Reputation: 13700

SELECT dateadd(month,datediff(month,0,getdate())-12,0)

Result is

-----------------------
2014-08-01 00:00:00.000

So the where clause should be

WHERE datecol >=dateadd(month,datediff(month,0,getdate())-12,0)

to get all data starting from jan 01 of last year's same month

Upvotes: 14

Felix Pamittan
Felix Pamittan

Reputation: 31879

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'))

Upvotes: 8

Related Questions