Answered by:
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.
Thursday, March 24, 2011 9:27 AM
Answers
-
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/- Marked as answer by Sam233 Thursday, March 24, 2011 9:38 AM
Thursday, March 24, 2011 9:35 AMAnswerer
All replies
-
You can make your WHERE clause like this:
WHERE date_field between getdate() and getdate()-365
~Manoj (manub22@gmail.com)
http://sqlwithmanoj.wordpress.com
MCCA 2011- Proposed as answer by Manoj Pandey (manub22)Microsoft employee Tuesday, August 5, 2014 4:43 AM
Thursday, March 24, 2011 9:30 AM -
Or
WHERE date_field between getdate() and dateadd(m,-12,getdate()
~Manoj (manub22@gmail.com)
http://sqlwithmanoj.wordpress.com
MCCA 2011Thursday, March 24, 2011 9:32 AM -
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/- Marked as answer by Sam233 Thursday, March 24, 2011 9:38 AM
Thursday, March 24, 2011 9:35 AMAnswerer -
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.000thanks
Thursday, March 24, 2011 9:36 AM -
thanks UriThursday, March 24, 2011 9:38 AM