locked
Get data for previous month RRS feed

  • Question

  • I have a dataset with a date field, which look like the example below…

     

    But what I need to do is create a WHERE clause on the date field where I only select all data from the previous month, so for example if I ran the query today (01/06/2011) I want to pull all records from the 01/05/2011 to 31/05/2010

     

    Is this possible?

    Wednesday, June 1, 2011 8:32 AM

Answers

  • WHERE dt>= DATEADD(m,DATEDIFF(m,0,GETDATE())-1,0),
    AND dt<DATEADD(m,DATEDIFF(m,0,GETDATE()),0)

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by SQL_1980 Wednesday, June 1, 2011 8:46 AM
    Wednesday, June 1, 2011 8:35 AM
    Answerer

All replies

  • WHERE dt>= DATEADD(m,DATEDIFF(m,0,GETDATE())-1,0),
    AND dt<DATEADD(m,DATEDIFF(m,0,GETDATE()),0)

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by SQL_1980 Wednesday, June 1, 2011 8:46 AM
    Wednesday, June 1, 2011 8:35 AM
    Answerer
  • How do I get the month name from the dataset too?

    so for example... as i'm bring back the dataset for the last month how can i get the month name and year?

    so in my reporting services page i want to add a text box and how the month name

    ="Monthly snapshot for " + monthname

    Wednesday, June 1, 2011 8:51 AM
  • SELECT DATENAME(month,GETDATE())
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, June 1, 2011 9:22 AM
    Answerer