Advertisements
Home > SQL Server Questions, SQL Tips > How to retrieve last 12 months records form a table – MSDN TSQL forum

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


–> 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
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: