CODEWITHSUNDEEP
sqlsql-server-2008date
Ofer Gozlan
Ofer Gozlan

Reputation: 1143

SQL - how to get data from previous month

i am using sql server 2008. i would like to get all the data (at the 'where' clause) from the previous month to the moment the query runs. so for example if today is 14.8.2014 it will collect all the information between 1.7.2014 to 31.7.2014

Upvotes: 0

Views: 963

Answers (3)

Paul Maxwell
Paul Maxwell

Reputation: 35613

Don't try for the last day of month, it is both easier and more reliable to use "1st day of Next Month" like this (note the use of less than):

    select
    *
    from tables
    where (
            dateField >= "1st of this Month"
              and
            dateField < "1st of Next Month"
          (

calculations:

    SELECT
          GETDATE()
          AS "getdate with time"

        , DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)
          AS "getdate time truncated"

        , DATEADD(dd, -(DAY(GETDATE()) - 1), DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0))
          AS "day 1 this month"

        , DATEADD(MONTH, 1, DATEADD(dd, -(DAY(GETDATE()) - 1), DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)))
          AS "day 1 next month"
    ;

so:

   select
    *
    from tables
    where (
            dateField >= DATEADD(dd, - (DAY(getdate()) - 1), DATEADD(dd, DATEDIFF(dd,0, getDate()), 0)) -- "1st of this Month"
              and
            dateField < DATEADD(month,1,DATEADD(dd, - (DAY(getdate()) - 1), DATEADD(dd, DATEDIFF(dd,0, getDate()), 0))) -- "1st of Next Month"
          (

Upvotes: 0

Ofer Gozlan
Ofer Gozlan

Reputation: 1143

that what solved it:

WHERE DATEPART(m, date_created) = DATEPART(m, DATEADD(m, -1, getdate())) AND DATEPART(yyyy, date_created) = DATEPART(yyyy, DATEADD(m, -1, getdate()))

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270873

Here is a simple way:

where year(datecol) * 12 + month(datecol) = year(getdate()) * 12 + month(datecol) - 1

This expression is not "sargable", meaning that the query cannot take advantage of an index. If you have a large table and this is important, then you can do date arithmetic:

where datecol >= dateadd(month, -1, cast( (getdate() - datepart(day, getdate()) + 1) as date)) and
      datecol < cast( (getdate() - datepart(day, getdate()) + 1) as date)

Upvotes: 3

Related Questions