locked
Retrieve last 12 months data RRS feed

  • 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 AM
    Answerer

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
    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 2011
    Thursday, 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 AM
    Answerer
  • 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

    Thursday, March 24, 2011 9:36 AM
  • thanks Uri
    Thursday, March 24, 2011 9:38 AM