Advertisements

Archive

Archive for March 24, 2011

How to retrieve last 12 months records form a table – MSDN TSQL forum

March 24, 2011 Leave a comment

–> Question:

I have a data field in my table that i would to use within my WHERE clause, to get the last 12 months dataset.

So for example, WHERE date between ‘20110323’ and ‘20100323’

Is this possible? I know I have to use something like GETDATE() or something but not quite sure how to incorporate this.
 

–> My Answer:

You can make your WHERE clause like this:

WHERE date_field between getdate() and getdate()-365

-- OR

WHERE date_field between getdate() and dateadd(m,-12,getdate()

 

–> Answer by Uri Dimant:

You can make your WHERE clause like this:

SELECT * 
FROM tbl 
WHERE dt >= DATEADD(month,-12,DATEADD(day,DATEDIFF(day,0,GETDATE()),0)) 
AND dt <=DATEADD(day,DATEDIFF(day,0,GETDATE()),0)

 

Ref link.


Advertisements