Retrieve last 12 months data

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.

Answer 1

You can make your WHERE clause like this:

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

 

---

~Manoj ([e-mail])
http://sqlwithmanoj.wordpress.com
MCCA 2011

Answer 2

Or 

 

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

 

---

~Manoj ([e-mail])
http://sqlwithmanoj.wordpress.com
MCCA 2011

Answer 3

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

 

 

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 4

cant seem to retrieve any data using the getdate()

my date field is a DATETIME datatype and below are some examples of how the data looks like

2006-06-15 00:00:00.000
2007-07-23 00:00:00.000
2007-04-16 00:00:00.000
2006-12-04 00:00:00.000

thanks

Answer 5

thanks Uri